how to get user list with database role of all database at a time
-
Hi,
I like to get user list with database role of all database at a time
I tried it with msforeach proc but not worked ,I tried to create a proc with below query
SELECT SP1.[name] AS 'Login', SP2.[name] AS 'DatabaseRole'
FROM sys.database_principals SP1
JOIN sys.database_role_members SRM
ON SP1.principal_id = SRM.member_principal_id
JOIN sys.database_principals SP2
ON SRM.role_principal_id = SP2.principal_id where SP2.name ='db_owner'
please help me to get this inforamtion at a time for each database
Regards,
Shivrudra W -
Hi there,
I've amended your query to run across all databases on a particular instance:
/*Create a temporary table to hold all result sets*/
CREATE TABLE #Table
(TableID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
,DatabaseName VARCHAR(20)
,LoginName VARCHAR(50)
,DatabaseRole VARCHAR(20)
)
/*Create command to run on each database*/
DECLARE @cmd varchar(max)
SET @Cmd =
'USE ?;
INSERT INTO #Table (DatabaseName, LoginName, DatabaseRole)
SELECT DB_NAME() [DatabaseName], SP1.[name] AS ''LoginName'', SP2.[name] AS ''DatabaseRole''
FROM sys.database_principals SP1
JOIN sys.database_role_members SRM
ON SP1.principal_id = SRM.member_principal_id
JOIN sys.database_principals SP2
ON SRM.role_principal_id = SP2.principal_id where SP2.name =''db_owner'''
/*Run command*/
EXECUTE sp_msforeachdb @cmd
/*View results*/
SELECT * FROM #Table
Hope this helps,
Rich
Hope this helps,
Rich[p]
[/p] -
sorry for delay to say .....Thanks
it really helped me.
Regards,
Shivrudra W
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply