October 6, 2014 at 9:12 am
Hi,
Unable to pass the select statement to cursor to get details across each databases.
DECLARE @DB_Name varchar(20)
DECLARE @Command nvarchar(max)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb');
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'SELECT ['+ dRole.name +'] AS [Database Role Name], ['+dPrinc.name +'] AS [Members]'+
'FROM sys.database_role_members AS ['+dRo+']
JOIN sys.database_principals AS ['+dPrinc+']
ON dRo.member_principal_id = '+dPrinc.principal_id+'
JOIN sys.database_principals AS ['+dRole+']
ON dRo.role_principal_id = '+dRole.principal_id+'
where dPrinc.name in ('''domain\XYZ'','''abcd''');
EXEC sp_executesql @Command
--print @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
Thanks.
October 6, 2014 at 9:18 am
What do you mean by 'unable'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 6, 2014 at 10:19 am
For starters, not sure why you did the dynamic code in the manner you did it. It's a lot of extra work.
You are not using the @DB_Name parameter anywhere. How do you expect to actually be "able" to do things?
Try this:
SELECT @Command = 'SELECT dRole.name AS [Database Role Name], dPrinc.name AS [Members]
FROM ' + @DB_Name + '.sys.database_role_members AS [dRo]
JOIN ' + @DB_Name + '.sys.database_principals AS [dPrinc]
ON dRo.member_principal_id = dPrinc.principal_id
JOIN sys.database_principals AS [dRole]
ON dRo.role_principal_id = dRole.principal_id
where dPrinc.name in ('''domain\XYZ'','''abcd''');
EXEC sp_executesql @Command
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
October 6, 2014 at 11:12 am
SQL-DBA-01 (10/6/2014)
Hi,Unable to pass the select statement to cursor to get details across each databases.
You need to accommodate use dbname command in dynamic query.
Here it is
DECLARE @DB_Name varchar(20)
DECLARE @Command nvarchar(max)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb');
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'USE [' + @DB_Name + '];SELECT dRole.name AS [Database Role Name], dPrinc.name AS [Members]'+
'FROM sys.database_role_members AS [dRo]
JOIN sys.database_principals AS [dPrinc] ON dRo.member_principal_id = dPrinc.principal_id
JOIN sys.database_principals AS [dRole] ON dRo.role_principal_id = dRole.principal_id
where dPrinc.name in (''yourusername'')';
EXEC sp_executesql @Command
--print @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
or you could use this to find roles and their role members in each database.
--sp_MSforeachdb is undocumented command, use at your risk/interest
EXEC sp_MSforeachdb ' use [?];SELECT
[ServerName] = @@servername,
[DBName] = db_name(),
[RoleName] = USER_NAME(drm.role_principal_id)
, [RoleMemberName] = USER_NAME(drm.member_principal_id)
FROM sys.database_role_members drm
ORDER BY [RoleName] ASC'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply