george sibbald (4/10/2014)
if you just want database roles users have granted use sp_helprolemember.This could be put in a cursor which loops through all your databases
Thanks George....
I need the users in database who have Sys admin access on the instance also....basically we are looking for the user roles, like dbowner, sys admin, dbreader, db write etc on a database level....
I tried using cursor but i'm missing something....i'm not very good at sql development...
can someone help me on this...
Declare @name varchar(100)
Declare @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
set @sqlstatement = 'Declare users_cursor CURSOR FOR Select name from sys.databases'
exec sp_executesql @sqlstatement
OPEN users_cursor
FETCH NEXT FROM users_cursor
INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
Print @name
SELECT DB_Name() as [Database Name], dp2.name UserName,dp2.type_desc UserType, 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'
FETCH NEXT FROM users_cursor --have to fetch again within loop
INTO @name
END
CLOSE users_cursor
DEALLOCATE users_cursor