Problem
I have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don’t want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what’s the best way to extract the information out of them?
Solution
After you’ve audited logins, as detailed in a previous tip, you’ll want to look at auditing server permissions and server roles. Depending on your version of SQL Server, there are different approaches you’ll have to take. For SQL Server 2000 and earlier, permissions at the server level are granted only through pre-existing server roles, otherwise known as fixed server roles. Starting with SQL Server 2005, permissions can be assigned outside of the roles, meaning we’ll have to do a bit more querying to capture all of the information needed. Let’s look at each version separately.
SQL Server 2000
For SQL Server 2000, we’re only concerned with membership in the fixed server roles. The best way to get this information is through the use of the system stored procedure sp_helpsrvrolemember. While this stored procedure can take parameters, if we want members of every server role, we can simply issue the stored procedure without parameters like so:
EXEC sp_helpsrvrolemember;
The problem with just using the stored procedure, though, is we have no control over sorting or anything else. The best way to handle this, then, is to dump the output of the stored procedure to a temporary table and then use it to query against, along with syslogins:
CREATE TABLE #ServerRoles (
ServerRole VARCHAR(20),
MemberName sysname,
sid VARBINARY(85)
);
INSERT INTO #ServerRoles
(ServerRole, MemberName, sid)
EXEC sp_helpsrvrolemember;
SELECT SL.name, SR.ServerRole
FROM syslogins SL
JOIN #ServerRoles SR
ON SL.sid = SR.sid
ORDER BY SL.name, SR.ServerRole;
DROP TABLE #ServerRoles;
Since there isn’t the capacity to grant specific permissions at the server level for SQL Server 2000, this is as far as we need to go.
SQL Server 2005 and later
Because SQL Server 2005 introduced a new security model where permissions can be assigned against Securables, we must delve a little deeper in the later versions to get all of the information we need for an audit. There are several catalog views we’re interested in:
- sys.server_principals
- sys.server_permissions
- sys.server_role_members
To get the server role members we’ll combine sys.server_principals and sys.server_role_members like so:
SELECT SP1.[name] AS 'Login', SP2.[name] AS 'ServerRole'
FROM sys.server_principals SP1
JOIN sys.server_role_members SRM
ON SP1.principal_id = SRM.member_principal_id
JOIN sys.server_principals SP2
ON SRM.role_principal_id = SP2.principal_id
ORDER BY SP1.[name], SP2.[name];
To get the permissions, we’ll key in on sys.server_principals and sys.server_permissions:
SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name AS 'ServerPermission'
FROM sys.server_principals SP
JOIN sys.server_permissions SPerm
ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission];
Do note, that if you’re not interested in the CONNECT SQL permission, you can filter that out using an appropriate WHERE clause – WHERE NOT (SPerm.type = ‘COSQ’ AND SPerm.state = ‘G’) – which will eliminate that from the result set. Of course, we could combine the two to generate a query for a single report. Since we know there will be no duplications between the two SELECT statements, we can use UNION ALL and a slight modification to the first query (to indicate it’s a role) to get the aggregate permissions for each login.
SELECT SP1.[name] AS 'Login', 'Role: ' + SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP1
JOIN sys.server_role_members SRM
ON SP1.principal_id = SRM.member_principal_id
JOIN sys.server_principals SP2
ON SRM.role_principal_id = SP2.principal_id
UNION ALL
SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission'
FROM sys.server_principals SP
JOIN sys.server_permissions SPerm
ON SP.principal_id = SPerm.grantee_principal_id
ORDER BY [Login], [ServerPermission];
And we’re successfully auditing all the permissions at a server level with respect to SQL Server 2005 and later. Do note the COLLATE DATABASE_DEFAULT that is placed in the SELECT query for each second column. This is to prevent a collation conflict in the ORDER BY clause.
Next Steps
- Take the time to automate this collection process. Create some audit tables and setup Jobs to periodically collect the data and alert you to any changes.
- Review the tip related to login auditing
- Refer to these other tips related to security
