May 12, 2014 at 10:50 am
DECLARE AllDatabases CURSOR FOR
SELECT name FROM master.dbo.sysdatabases WHERE dbid > 4
OPEN AllDatabases
DECLARE @DBNameVar NVARCHAR(128),@Statement VARCHAR(300), @Statement2 varchar(400)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'CHECKING DATABASE ' + @DBNameVar
set @Statement = N'USE ' + @DBNameVar + CHAR(13)
+ N'SELECT db_name(), dRole.name, dPrinc.name
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 '
SET @Statement2 = 'database_principals.name = ''XYX''' <---Login account
-- database_principals.name like (' + @name +')'
exec (@Statement+@Statement2)
PRINT CHAR(13) + CHAR(13)
FETCH NEXT FROM AllDatabases INTO @DBNameVar
END
CLOSE AllDatabases
DEALLOCATE AllDatabases
P.S. Getting error....the query do not execute. Kindly suggest.
Thanks.
May 12, 2014 at 11:03 am
The error is here:
SET @Statement2 = 'database_principals.name = ''XYX''' <---Login account
Remove the less than sign (the '<')
May 13, 2014 at 12:36 am
gbritton1,
Did you run my query and removing "<" was that completed successfully?
I added that to show where the error is coming nothing else.
If you have any other suggestion, plz provide.
Thanks.
May 13, 2014 at 2:10 am
Change the EXEC to a print and examine the generated statement. That usually helps find errors in dynamic SQL.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply