sp_MSForEachdb syntax error

Question

Post reply

sp_MSForEachdb syntax error

LOOKUP_BI-756009

SSCertifiable

Points: 7286
More actions
November 1, 2010 at 3:10 pm

#388141

I need to execute the following script againts each database on a server to capture all the sql users and their permission. I got the 'select' script from a forum in sql central.The script gave me exactly what I need but when I try to add sp_msforeachdb functionallity I receive the following error msg,
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near 'principal_id'.
Script
DECLARE @cmd1 nvarchar(2000)
SET @cmd1 = 'IF ''?'' NOT IN(''master'', ''model'', ''tempdb'', ''msdb'')' + 'BEGIN '
+ 'Print ''Populating Audit table for ?...'';'
+ 'INSERT INTO [AW ].[dbo].[Audit]([Login Type],srvLogin,srvRole,dbUser,dbRole) ' +
+ 'Use ?
select
[Login Type]=
case sp.type
when ''u'' then ''WIN''
when ''s'' then ''SQL''
when ''g'' then ''GRP''
end,
convert(char(45),sp.name) as srvLogin,
convert(char(45),sp2.name) as srvRole,
convert(char(25),dbp.name) as dbUser,
convert(char(25),dbp2.name) as dbRole
from
sys.server_principals as sp join
sys.database_principals as dbp on [sp].[sid]=[dbp].[sid] join
sys.database_role_members as dbrm on [dbp].[principal_Id]=[dbrm].[member_principal_Id] join
sys.database_principals as dbp2 on [dbrm].[role_principal_id]=[dbp2].[principal_id] left join
sys.server_role_members as srm on [sp].[principal_id]=[srm].[member_principal_id] left join
sys.server_principals as sp2 on [srm].[role_principal_id]=[sp2].[principal_id]'
EXEC sp_MSForEachdb @cmd1
GO
** The Audit table that needs to be populated sits in the AW database and the select script needs to be executed againts all database. Im not sure if I have correct above.

Viewing 7 posts - 1 through 6 (of 6 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Try changing:

+ 'Use ?

to

+ 'Use [?]

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 2

I tried Use [?] and also Use ''?'' both returned the same error msg as below

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'Use'.

Msg 102, Level 15, State 1, Line 19

Incorrect syntax near 'principal_id'.

Here is what I get when i do print @cmd1

IF '?' NOT IN('master', 'model', 'tempdb', 'msdb')BEGIN Print 'Populating Login Audit table for ?...'; INSERT [AW].[dbo].[Audit]([Login Type],srvLogin,srvRole,dbUser,dbRole) Use [?]

select

[Login Type]=

case sp.type

when 'u' then 'WIN'

when 's' then 'SQL'

when 'g' then 'GRP'

end,

convert(char(45),sp.name) as srvLogin,

convert(char(45),sp2.name) as srvRole,

convert(char(25),dbp.name) as dbUser,

convert(char(25),dbp2.name) as dbRole

from

sys.server_principals as sp join

sys.database_principals as dbp on [sp].[sid]=[dbp].[sid] join

sys.database_role_members as dbrm on [dbp].[principal_Id]=[dbrm].[member_principal_Id] join

sys.database_principals as dbp2 on [dbrm].[role_principal_id]=[dbp2].[principal_id] left join

sys.server_role_members as srm on [sp].[principal_id]=[srm].[member_principal_id] left join

sys.server_principals as sp2 on [srm].[role_principal_id]=[sp2].[principal_id]

LOOKUP_BI-756009 SSCertifiable Points: 7286 More actions · Answer 3

I have changed my code a little as well and now I get different error message

DECLARE @cmd1 nvarchar(2000)

SET @cmd1 = 'IF ''?'' NOT IN(''master'', ''model'', ''tempdb'', ''msdb'')' + 'BEGIN '

+ 'Print ''Populating Login Audit table for ?...''; '

+ 'INSERT INTO [AW]..[Audit]([Login Type],srvLogin,srvRole,dbUser,dbRole) ' +

'select

[Login Type]=

case sp.type

when ''u'' then ''WIN''

when ''s'' then ''SQL''

when ''g'' then ''GRP''

end,

convert(varchar(80),sp.name) as srvLogin,

convert(varchar(50),sp2.name) as srvRole,

convert(varchar(50),dbp.name) as dbUser,

convert(varchar(50),dbp2.name) as dbRole

from

''?''.sys.server_principals as sp join

''?''.sys.database_principals as dbp on [sp].[sid]=[dbp].[sid] join

''?''.sys.database_role_members as dbrm on [dbp].[principal_Id]=[dbrm].[member_principal_Id] join

''?''.sys.database_principals as dbp2 on [dbrm].[role_principal_id]=[dbp2].[principal_id] left join

''?''.sys.server_role_members as srm on [sp].[principal_id]=[srm].[member_principal_id] left join

''?''.sys.server_principals as sp2 on [srm].[role_principal_id]=[sp2].[principal_id]'+

'END'

print @cmd1

EXEC sp_MSForEachdb @cmd1

GO

Error