Below is what I recommend to my clients to evaluate the security of their infrastructure quarterly, although more frequently is always better. Typically with this set of scripts, you’ll want to either compile the data into workbooks, flat files, or at least historic tables so you can track trends and changes.
We begin with two queries; one brings back the SQL Server Name and Instance so we know what we are evaluating and then we check the version of SQL Server which in itself has its own security repercussions based on service pack and year.
SELECT @@SERVERNAME AS 'SQL Server Name and Instance' SELECT @@VERSION AS 'SQL Server Version'
The next script brings us a simple select of SQL Logins on the server. It’s a nice view of what logins exist, is our policy correctly enforced, do they have expired passwords, are the accounts locked out, and other helpful tidbits. Some additional reading on the returned records can be found here: https://msdn.microsoft.com/en-us/library/ms345412.aspx
SELECT name ,
Is_Policy_Checked ,
Is_Expiration_Checked ,
LOGINPROPERTY(name, 'IsMustChange') AS Is_Must_Change ,
LOGINPROPERTY(name, 'IsLocked') AS [Account Locked] ,
LOGINPROPERTY(name, 'LockoutTime') AS LockoutTime ,
LOGINPROPERTY(name, 'PasswordLastSetTime') AS PasswordLastSetTime ,
LOGINPROPERTY(name, 'IsExpired') AS IsExpired ,
LOGINPROPERTY(name, 'BadPasswordCount') AS BadPasswordCount ,
LOGINPROPERTY(name, 'BadPasswordTime') AS BadPasswordTime ,
LOGINPROPERTY(name, 'HistoryLength') AS HistoryLength ,
Modify_date
FROM sys.sql_logins;
This next query brings us a high level overview of what the logins have access to instance wide. It’s a great check for who is sysadmin or not.
SELECT Name ,
DenyLogin ,
HasAccess ,
SysAdmin ,
SecurityAdmin ,
ServerAdmin ,
SetupAdmin ,
ProcessAdmin ,
DiskAdmin ,
DBCreator ,
BulkAdmin ,
UpdateDate
FROM syslogins
ORDER BY name;
We then break this down to how they have permissions. Is it a SQL or Windows account? What type of permissions are adjusted and what are our datestamps on these logins? Those questions are answered with this script, it’s a more granular approach to the query we previously looked at.
;WITH ServerPermsAndRoles
AS ( SELECT spr.name AS login_name ,
spr.type_desc AS login_type ,
spm.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS AS permission_name ,
-- CONVERT(NVARCHAR(1), spr.is_disabled) AS is_disabled ,
'permission' AS permission_type ,
spr.create_date AS create_date ,
spr.modify_date AS modify_date
FROM sys.server_principals spr
INNER JOIN sys.server_permissions spm ON spr.principal_id = spm.grantee_principal_id
WHERE spr.type IN ( 's', 'u' )
UNION ALL
SELECT sp.name AS login_name ,
sp.type_desc AS login_type ,
spr.name AS permission_name ,
-- CONVERT(NVARCHAR(1), spr.is_disabled) AS is_disabled ,
'role membership' AS permission_type ,
spr.create_date AS create_date ,
spr.modify_date AS modify_date
FROM sys.server_principals sp
INNER JOIN sys.server_role_members srm ON sp.principal_id = srm.member_principal_id
INNER JOIN sys.server_principals spr ON srm.role_principal_id = spr.principal_id
WHERE sp.type IN ( 's', 'u' )
),
MapDisabled
AS ( SELECT
DISTINCT SP.NAME ,
SP.IS_DISABLED
FROM SYS.server_principals SP
FULL JOIN sys.server_role_members RM ON SP.PRINCIPAL_ID = RM.MEMBER_PRINCIPAL_ID
WHERE SP.type IN ( 's', 'u' )
)
SELECT ServerPermsAndRoles.Login_Name ,
ServerPermsAndRoles.Login_Type ,
ServerPermsAndRoles.Permission_Name ,
ServerPermsAndRoles.Permission_Type ,
ServerPermsAndRoles.Create_Date ,
ServerPermsAndRoles.Modify_Date ,
MapDisabled.Is_Disabled
FROM ServerPermsAndRoles
LEFT JOIN MapDisabled ON MapDisabled.name = ServerPermsAndRoles.login_name
ORDER BY MapDisabled.is_disabled DESC ,
ServerPermsAndRoles.login_name;
Now we see what those logins are mapped to and what permissions and what databases are impacted. You can see how many people have X, Y, or Z access to your databases and what’s mapped where.
DECLARE @DBRolePermissions TABLE( DatabaseName varchar(300), Principal_Name sysname, Login_Name sysname NULL, DB_RoleMember varchar(300), Permission_Type sysname); INSERT INTO @DBRolePermissions EXEC sp_MSforeachdb ' SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, roles.Name AS Role_Member_Name, roles.type_desc FROM [?].sys.database_role_members r LEFT OUTER JOIN [?].sys.database_principals users on r.member_principal_id = users.principal_id LEFT OUTER JOIN [?].sys.database_principals roles on r.role_principal_id = roles.principal_id --JOIN [?].sys.database_permissions Action on Action.grantee_principal_id = users.principal_id' INSERT INTO @DBRolePermissions EXEC sp_msforeachdb ' SELECT DISTINCT ''?'' AS DatabaseName, users.Name AS UserName, suser_sname(users.sid) AS Login_Name, r.Permission_Name AS DB_RoleMember, r.class_desc FROM [?].sys.database_permissions r LEFT OUTER JOIN [?].sys.database_principals users on r.grantee_principal_id = users.principal_id WHERE r.class_desc = ''DATABASE''' SELECT DISTINCT Principal_Name, Login_Name, DatabaseName, DB_RoleMember AS Permission_Name FROM @DBRolePermissions WHERE Permission_Type <> 'DATABASE' ORDER BY Principal_Name, DatabaseName, DB_RoleMember;
After evaluating who has access to what, I like to check and verify what databases are encrypted. This is a quick check to see what is and isn’t encrypted at rest and the strength setup.
USE master;
GO
SELECT db.name,
db.is_encrypted,
dm.encryption_state,
dm.percent_complete,
dm.key_algorithm,
dm.key_length
FROM sys.databases db
LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id;
The last check I do is seeing what the linked server and remote server permissions are like to keep tabs on our other endpoints.
SELECT ss.server_id
,ss.name
,'Server ' = Case ss.Server_id
when 0 then 'Current Server'
else 'Remote Server'
end
,ss.product
,ss.provider
,ss.catalog
,'Local Login ' = case sl.uses_self_credential
when 1 then 'Uses Self Credentials'
else ssp.name
end
,'Remote Login Name' = sl.remote_name
,'RPC Out Enabled' = case ss.is_rpc_out_enabled
when 1 then 'True'
else 'False'
end
,'Data Access Enabled' = case ss.is_data_access_enabled
when 1 then 'True'
else 'False'
end
,ss.modify_date
FROM sys.Servers ss
LEFT JOIN sys.linked_logins sl
ON ss.server_id = sl.server_id
LEFT JOIN sys.server_principals ssp
ON ssp.principal_id = sl.local_principal_id;
To bring it all together, it’s important to generate this data as a snapshot in time so you can compare it or write automated reporting to compare off this data so you can see how your environment is changing and who can access what. When in charge of a SQL Server, you need to keep track of who can do what and ensure the proper folks have proper permissions so you don’t run into compliance issues and minimize user impact on the databases.