Listing server and database roles for all logins and users in SQL Server – Part 1 – Local Server

I was approached with a unique problem to solve; find out who has what permissions on my Database Servers.  This is the first post in a three part series about finding all user permissions on your SQL Server instance. The next posts will build upon this and introduce a way to use the same queries to get permissions from remote servers.  In the final post, I will create a report that will nicely display the results of all those queries making it easy to audit and fix security problems. The granularity I’m going for is mapping logins/users to their corresponding roles at the instance and database level.

First things first. When you build a house you need a foundation.  Our final goal is to build an easy to use report so that we have a method of auditing security easily with a friendly interface. So let’s go ahead and build the foundation for our report by creating three simple tables:


/***NOTE: The database I use for Reporting Metadata is called SSRSReportingDB. You will either have to create this database or change your USE statement.
My recommendation is to create this database since future posts will use the DB name for inserts ******/

USE [SSRSReportingDB]
GO
CREATE TABLE [dbo].[ServerPermissions](
[UserName] [sysname] NOT NULL,
[UserRole] [sysname] NOT NULL,
[Servername] [nvarchar](128) NULL,
[LastUpdate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ServerPermissions] ADD CONSTRAINT [DF_ServerPermissions_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]
GO

CREATE TABLE [dbo].[DatabasePermissions](
[UserName] [sysname] NOT NULL,
[UserRole] [sysname] NOT NULL,
[DatabaseName] [nvarchar](128) NULL,
[Servername] [nvarchar](128) NULL,
[LastUpdate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DatabasePermissions] ADD CONSTRAINT [DF_DatabasePermissions_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]
GO

CREATE TABLE [dbo].[DatabaseNames](
[name] [nvarchar](128) NOT NULL
) ON [PRIMARY]
GO


Alright now that we have our tables created lets start filling them up.  First, let’s go with the whole forest view and get information about our server then work our way down into our databases. So let’s start by getting your server permissions.  The following stored procedure will use dynamic SQL to pull back information about various users and the roles they have on your server.


CREATE PROCEDURE pr_GetAllServerPermissions
(@Servername varchar(100))
as
BEGIN
DECLARE @SQL as varchar(max)
SET @SQL =
'INSERT into SSRSReportingdb.dbo.ServerPermissions (UserName,UserRole,Servername)
SELECT p2.name as UserName, p.name as UserRole, '''+@Servername+''' as Servername
FROM ' +@Servername+
'.master.sys.server_role_members r
INNER join '+@Servername+'.master.sys.server_principals p on r.role_principal_id=p.principal_id
INNER join '+@Servername+'.master.sys.server_principals p2 on p2.principal_id=r.member_principal_id
WHERE p2.name not like ''%NT %\%''
ORDER by p2.name'
PRINT @SQL
EXEC (@SQL)

END


The next order of business is to get the individual database permissions. The stored procedure below will use dynamic SQL to bring back a list of all your ONLINE databases and insert them in to the DatabaseNames table you created earlier. Optionally, you could just use a temp or table variable to store this information but since the information is not going to be that much I prefered to go with a regular table that I truncate every time I run the procedure.

After the database names are loaded, a cursor runs through each of the databases and brings back all the database users and their permissions. There are two sets of dynamic sql that do this; the first is for getting data from the local server and the second is for remote servers. For now, do not attempt to run this stored procedure for remote servers.


CREATE PROCEDURE pr_GetAllDatabasePermissions

@Servername varchar(100)

as
Begin

Truncate Table DatabaseNames

DECLARE @SQLCursor varchar(max)

SET @SQLCursor =
'INSERT into SSRSReportingDB.dbo.DatabaseNames
SELECT name
FROM '+@servername+'.master.sys.databases
WHERE state_desc = ''ONLINE'' and name not in (''master'',''tempdb'',''model'',''distribution'')'

PRINT @SQLCursor
Exec (@SQLCursor)
DECLARE @databasename as varchar(200)
DECLARE @SQL as varchar(max)

DECLARE cr_dbnames cursor
FOR SELECT name FROM DatabaseNames ORDER BY name
OPEN cr_dbnames
FETCH NEXT FROM cr_dbnames INTO @databasename
WHILE (@@Fetch_Status <> -1)
BEGIN

-----Section to load information for LOCAL Server
IF @@SERVERNAME = @Servername
BEGIN
set @SQL = '
INSERT into SSRSReportingdb.dbo.DatabasePermissions (UserName,UserRole,DatabaseName,Servername)
SELECT p2.name as UserName, p.name as UserRole, '''+@databasename+''' as DatabaseName, '''+@Servername+''' as Servername
FROM '+@DatabaseName+'.sys.database_role_members r
INNER join '+@DatabaseName+'.sys.database_principals p on r.role_principal_id=p.principal_id
INNER join '+@DatabaseName+'.sys.database_principals p2 on p2.principal_id=r.member_principal_id
WHERE p2.name <> ''dbo''
ORDER by p2.name'
END
----SECTION TO LOAD INFORMATION FROM REMOTE SERVERS
ELSE
BEGIN
SET @sql = '
INSERT into SSRSReportingdb.dbo.DatabasePermissions (UserName,UserRole,DatabaseName,Servername)
SELECT *
FROM OPENQUERY('+@servername+',''
SELECT p2.name as UserName, p.name as UserRole, '''''+@databasename+''''' as DatabaseName, '''''+@servername+''''' as Servername
FROM ['+@DatabaseName+'].sys.database_role_members r
INNER join ['+@DatabaseName+'].sys.database_principals p on r.role_principal_id=p.principal_id
INNER join ['+@DatabaseName+'].sys.database_principals p2 on p2.principal_id=r.member_principal_id
WHERE p2.name <> ''''dbo''''
ORDER by p2.name'')'
END

PRINT @SQL
EXECUTE (@SQL)
--/*Older Code - would require permissions for Read only user on every DB on the server*/
/*
set @SQL = '
INSERT into SSRSReportingdb.dbo.DatabasePermissions (UserName,UserRole,DatabaseName,Servername)
SELECT p2.name as UserName, p.name as UserRole, '''+@databasename+''' as DatabaseName, '''+@Servername+''' as Servername
FROM ' +@Servername+
'.'+@DatabaseName+'.sys.database_role_members r
INNER join '+@Servername+'.'+@DatabaseName+'.sys.database_principals p on r.role_principal_id=p.principal_id
INNER join '+@Servername+'.'+@DatabaseName+'.sys.database_principals p2 on p2.principal_id=r.member_principal_id
WHERE p2.name <> ''dbo''
ORDER by p2.name
'
Print @SQL
exec (@SQL)
*/

FETCH NEXT FROM cr_dbnames INTO @databasename
END

CLOSE cr_dbnames
DEALLOCATE cr_dbnames

END


For now run this procedure using your local server and in the next post I will demonstrate how to get permissions for remote servers using Linked Servers.
**Regarding the commented out code in the last stored procedure, if you prefer not to use OpenQuery you can use that code. However, you must give your readonly user that you will use for your Linked Server read permission on all databases on every remote server you are going to access. If there is a work around, please feel free to share it**