sp_MSForEachdb syntax problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sp_MSForEachdb syntax problem

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-11-01 : 17:17:53

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-02 : 03:38:00

What does this return?

print @cmd1

Madhivanan

Failing to plan is Planning to fail

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-11-02 : 09:37:58

Here is what I get when i print

IF '?' NOT IN('master', 'model', 'tempdb', 'msdb')BEGIN Print 'Populating Login 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]
print @cmd1

[b]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'.[/b]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-02 : 10:02:21

if
begin
end

No, you're never too old to Yak'n'Roll if you're too young to die.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-11-02 : 10:58:50

I have added the 'END' and 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
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'master'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'tempdb'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'model'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'msdb'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'ReportServer'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'ReportServerTempDB'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'AdventureWorksDW2008R2'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'AdventureWorksLT2008R2'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'AdventureWorks'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'AdventureWorksDW'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 'AdventureWorksLT'.

1. I want to run a single query that give back a result set of all the sql users and permission a login has in all the databases in one server
2. Next, I want to feed this information to a table [AW]..[Audit] in a specific database and store it there.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2010-11-02 : 12:13:56

Got it fixed, replace ''?'' with '

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources