Unable to pass the select statement to cursor to get details across each databases.

Question

Unable to pass the select statement to cursor to get details across each databases.

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
October 6, 2014 at 9:12 am

#291301

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael L John One Orange Chip Points: 27055 More actions · Answer 2

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/

SumLasty SSCommitted Points: 1923 More actions · Answer 3

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'