Collecting Server Logins & DB users Granted Permissions (Part 1)

In this post I will show you a quick way to collect the permissions granted to SQL Server logins and its associated database users in order to give the DBA a full picture about the permissions map for the SQL instances he\she manage. 

What we need to collect is simply the following:

1. Server logins (Windows & SQL) and its assigned server level roles and permissions

2. Database users for these server logins and its assigned database level roles and permissions

In this post we will collect the first part (server logins) and will follow this with another post for database users part.

Then let us start to check what kind of information we need to collect here:

1. SQL instance name --> the targeted SQL instance

2. Login name --> server logins for all types

3. Login type --> login type (SQL, Windows user, Windows group ...etc.)

4. Server role --> server role granted to the server login

5. Server permission --> server permission granted to server login

And in order to collect this information we will query the below catalog views:

sys.server_role_members a catalog view that contains information about each member of each fixed server role

sys.server_principals a catalog view that contains information about server logins and its type, status and created date

sys.server_permissions a catalog view that contains information about server permissions and its associated logins

The first step is to collect server logins and its assigned server roles

SELECT sys.server_role_members.role_principal_id, [role].name AS RoleName,

sys.server_role_members.member_principal_id, member.name AS MemberName

INTO #ServRole

FROM sys.server_role_members

RIGHT JOIN sys.server_principals AS [role]

ON sys.server_role_members.role_principal_id = role.principal_id

RIGHT JOIN sys.server_principals AS member

ON sys.server_role_members.member_principal_id = member.principal_id

Then collecting the assigned permissions for the server logins

SELECT SR.name,SR.type_desc,SP.[permission_name]

INTO #ServPerm

FROM sys.server_permissions SP RIGHT join sys.server_principals SR ON SP.grantee_principal_id=SR.principal_id

WHERE SR.name not like '#%' and SP.class=100 and SP.[type]<>'COSQ' and SP.[state]='G' and SR.type_desc<>'SERVER_ROLE'

The last step then is to insert both collected parts “server roles & server permissions” into a temporary table #Logins

CREATE TABLE #Logins (SQLInstName varchar(max),LoginName varchar(max),LoginType varchar(max), ServRole varchar(max),ServPerm varchar(max))

GO

INSERT INTO #Logins (SQLInstName , LoginName, ServRole,LoginType)

SELECT @@servername AS 'SQL_Instance', L.MemberName,L.RoleName, P.type_desc  FROM #ServRole L join sys.server_principals P

ON L.MemberName=P.name ORDER BY L.RoleName ASC, L.MemberName ASC

INSERT INTO #Logins

SELECT @@SERVERNAME, name, type_desc,NULL, [permission_name]

FROM #ServPerm

DROP TABLE #ServRole

DROP TABLE #ServPerm

SELECT * FROM #logins

DROP TABLE #Logins

This is a sample of the result set for this script, let is go through it ...

We can see that "TestSec" login is a securityadmin and also has the alter any database server permission, "test" login has no server roles but has two server permissions (alter any database and alter trace) while a login like "owner" for example has no server roles or server permissions.

Next Step:

In the next post we will collect the database users granted permissions and roles.