Prev: How to use scalar functions?
Next: Help on UPDATE from a novice
From: Joe K. on 17 Mar 2010 14:56

I am looking for SQL Server 2005 script, that will output all database roles
(db_datareader, db_datawriter,.etc) and application roles for all user
accounts and databases on the database server.

Please help me complete this task.

Thank You,
From: Tom on 17 Mar 2010 18:14
On Mar 17, 2:56 pm, Joe K. <J...(a)discussions.microsoft.com> wrote:
> I am looking for SQL Server 2005 script, that will output all database roles
> (db_datareader, db_datawriter,.etc) and application roles for all user
> accounts and databases on the database server.
>
> Please help me complete this task.
>
> Thank You,

A little more that you asked for, this includes server roles.

IF OBJECT_ID('tempdb..#RoleTable') IS NOT NULL
BEGIN DROP TABLE #RoleTable;
END;
GO
CREATE TABLE #RoleTable
( SID UNIQUEIDENTIFIER
,LoginName VARCHAR(255) DEFAULT ''
,LoginType VARCHAR(255) DEFAULT ''
,ServerRole VARCHAR(255) DEFAULT ''
,DatabaseName VARCHAR(255) DEFAULT ''
,UserName VARCHAR(255) DEFAULT ''
,UserType VARCHAR(255) DEFAULT ''
,DatabaseRole VARCHAR(255) DEFAULT ''
);
INSERT #RoleTable
( SID
,LoginName
,LoginType
,ServerRole
)
SELECT server_principals.sid
,server_principals.name
,server_principals.type_desc
,ServerRoles.name
FROM sys.server_principals server_principals
LEFT JOIN sys.server_role_members server_role_members ON
server_principals.principal_id =
server_role_members.member_principal_id
LEFT JOIN sys.server_principals ServerRoles ON
server_role_members.role_principal_id = ServerRoles.principal_id
WHERE server_principals.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN');

DECLARE @DatabaseName VARCHAR(255);
DECLARE @Statement NVARCHAR(MAX);
DECLARE DatabaseCursor CURSOR FOR
SELECT name
FROM sys.databases
FOR READ ONLY;
OPEN DatabaseCursor;
FETCH DatabaseCursor INTO @DatabaseName;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Statement =
'
INSERT #RoleTable
( SID
,DatabaseName
,UserName
,UserType
,DatabaseRole
)
SELECT server_principals.sid
,''' + @DatabaseName + '''
,database_principals.name
,database_principals.type_desc
,DatabaseRoles.name
from [' + @DatabaseName + '].sys.server_principals
server_principals
JOIN [' + @DatabaseName + '].sys.database_principals
database_principals ON server_principals.sid =
database_principals.sid
LEFT JOIN [' + @DatabaseName + '].sys.database_role_members
database_role_members ON database_principals.principal_id =
database_role_members.member_principal_id
LEFT JOIN [' + @DatabaseName + '].sys.database_principals
DatabaseRoles ON database_role_members.role_principal_id =
DatabaseRoles.principal_id
WHERE database_principals.type_desc IN (''SQL_USER'',
''WINDOWS_USER'');';
EXEC sp_executesql @Statement;
FETCH DatabaseCursor INTO @DatabaseName;
END
CLOSE DatabaseCursor;
DEALLOCATE DatabaseCursor;
SELECT *
FROM #RoleTable
ORDER BY SID ASC
,DatabaseName ASC;
 | 
Pages: 1
Prev: How to use scalar functions?
Next: Help on UPDATE from a novice