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

In the previous post we got the server logins associated with its server level permissions, this is a good step but by default it is not enough, because most of the time we will have some logins that have permissions on the database level not on the whole instance one.

In the below script we use a simple cursor to loop through the targeted instance databases and extract the database roles assigned to server logins, please note that using cursors is not the optimum method for looping for many considerations we will talk about later but at least make sure to close and de-allocate the cursor after using it.

The script we are using here is getting data from three catalog views:

sys.database_role_members is a catalog view that contains information about mapping between database user’s IDs and its assigned database role’s IDs.

sys.database_principals is a catalog view that contains information about database users and its type, status and created date.

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

SQL Code:

CREATE TABLE ##LoginUsers (SQLInstName varchar(max),LoginName varchar(max),DBName varchar(max),DBUser varchar(max), DBRole varchar(max))

DECLARE @name VARCHAR(50) -- database name

DECLARE db_cursor CURSOR FOR

select name from sys.databases

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 

BEGIN 

exec('USE [' +  @name + '];

insert into   ##LoginUsers

select @@servername, S.name as ''Login_Name'', db_name() as ''Database_Name'',D2.name AS ''DB_User'',D.name AS RoleName

from sys.database_role_members R join sys.database_principals D on  R.role_principal_id=D.principal_id

join sys.database_principals D2 on R.member_principal_id=D2.principal_id

join sys.server_principals S on D2.sid=S.sid

order by S.name asc')

       FETCH NEXT FROM db_cursor INTO @name 

END 

CLOSE db_cursor 

DEALLOCATE db_cursor

After that we can query this temp table in order to get any piece of required information, below is a snapshot of a sample result set for the query.

Next Step

It is useful to keep these security figures inside tables in order to have a security log for granted permissions on your SQL instance, this will be our next and final step in these three parts of Collecting Server Logins & DB users Granted Permissions series.