How to Check SQL Server Security Part 1

You know you need to be thinking about SQL Server security, but maybe you’re not sure where to start. Topics like firewalls and ports and port scanners and such may be dancing your mind. Those are good things to think about, but they are not under your sphere of influence as a data professional in charge of SQL Server. So, what can you do?

Your first place to start is by looking at the Logins, which as I’ve explained in a previous  post, are at the level of the SQL Server instance level.

The internet is a vast ocean of scripts on this topic so let me boil the ocean down to a couple of options for you.

Option 1:

A simple script to get the server Logins and their roles, ignoring the built in accounts that start with NT Service.

/*
Server principals and their role.
*/
Use master;
GO
SELECT  roles.principal_id AS RolePrincipalID,  
roles.name AS RolePrincipalName,  server_role_members.member_principal_id  AS MemberPrincipalID,  
members.name AS MemberPrincipalName

FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
INNER JOIN sys.server_principals AS members 
    ON server_role_members.member_principal_id = members.principal_id
WHERE members.name NOT LIKE 'NT SERVICE%';

The above script is a starting point. It’s also simple to understand and easy to use; i.e. it’s not overly complex and you see results like below.

Output Server Role Script

Option 2: Kenneth Fisher’s sp_SRVPermissions

This is a far more complex stored procedure than the script in option 1 and it digs deeper and provides more information. The output also provides some scripting that will save you time. Below are a couple of examples of how to run it from the DBAUtility database that I tend to install on SQL Servers. You can pass it a number of parameters but the most common ones are going to be either a role name you’re interested in knowing more about, Like SysAdmin, or a Principal name that you are curious to know more about,

/*  
Server permissions by role you're interested in.
*/
exec DBAUtility.dbo.sp_SrvPermissions @Role = 'sysadmin';

/*  
Server permissions by server principal you're interested in.
*/
exec DBAUtility.dbo.sp_SrvPermissions @Principal = 'MAPToolkit';

Here are some abbreviated results.

Results when passing a role name

Results when passing a Principal name

Some observations:

1. Passwords and sids are hashed and he says in his script comments that this works fine in SQL Server 2008 and above.
2. There are several other parameters for this procedure. Some let you look for things like SQL Logins, a Windows Group, Windows login and others. Some change the output  or allow the use of wildcard LIKE searches.
3. Note the DROP and the CREATE scripts in the shown output. I love this because it saves you time if you decide you want to drop or need to put a principal back.

Next Steps to Take

1. Download the stored procedure from Kenneth Fisher and experiment with it’s output to hep you manage SQL Server security.
2. If you have an idea for an improvement, make that known on his GitHub
3. If you have questions or comments, leave me a comment on this post, or message me on Twitter.