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