:::: MENU ::::

Tuesday, March 3, 2015

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
select name from sys.databases
OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 
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 

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.


Post a Comment