Listing DBs and their users with rights on SQL Server

Original post here: https://www.sqlservercentral.com/Forums/1560182/Script-to-get-the-list-of-users-and-permissions-in-a-database#bm1560813

With multiple result sets:

-- List out all users and user roles of all databases in a SQL Server instance
set nocount on
set quoted_identifier off
Declare @name varchar(100)
Declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
Declare users_cursor CURSOR FOR Select name from sys.databases where database_id > 4

OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Database ' + @name
set @sqlstatement = N'use [' + @name +']'+char(13)+N'select
convert(char(30),dp2.name) UserName,convert(char(20),dp2.type_desc) UserType, convert(char(20),dp.name) Role
FROM sys.database_principals dp
INNER JOIN sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <>'+'''R'''

exec sp_executesql @sqlstatement

FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @name
END
CLOSE users_cursor
DEALLOCATE users_cursor

Single result set using COALESCE-Operator:

--===== Declare a variable to hold the command we're going to build
DECLARE @MyCmd nVARCHAR(MAX)
declare @name NVARCHAR(200)

--===== Build the command to interrogate every database as if we were using a cursor.
-- If you want to include report servers, we'll need to do those separately
-- because of collation problems with some of the names. You'd have this same
-- problem if you used a cursor to insert into one table.
SELECT @MyCmd = COALESCE(@MyCmd+' UNION ALL'+CHAR(10),'')
+ 'select '''+Name+''' AS DBName,convert(char(30),dp2.name) UserName,convert(char(20),dp2.type_desc) UserType, convert(char(20),dp.name) Role
FROM '+Name+'.sys.database_principals dp
INNER JOIN '+Name+'.sys.database_role_members drm ON dp.principal_id = drm.role_principal_id
INNER JOIN '+Name+'.sys.database_principals dp2 ON drm.member_principal_id = dp2.principal_id
WHERE dp2.principal_id > 4 AND dp2.type <>'+'''R'''
FROM Master.Sys.DataBases
WHERE DataBase_ID > 5
and name not like 'report%'

--===== Display, then execute the cursor
PRINT @MyCmd
EXEC (@MyCmd)

Leave a Reply Cancel