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.
0 comments:
Post a Comment