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
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
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))
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]
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.
In the next post we will collect the database users granted permissions and roles.