EXEC sp_MsForEachDb Error - 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)

EXEC sp_MsForEachDb Error

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2012-06-21 : 16:20:06

I need to execute the following query againts all database in an instance. It would return me the list of objects and type of permissions .This script accepts a parameter (which is the users id)

DECLARE @COPYDatabaseUserName [sysname]
SET @COPYDatabaseUserName = 'Domain\Laurine Torrone'

EXEC sp_MsForEachDb @command1 =
'SELECT
S.[name],
S.[principal_id],
R.[role_principal_id],
''ROLE'' AS [role_name],
S.[default_database_name],
S.[default_language_name]
FROM ?.sys.server_principals S
INNER JOIN ?.sys.server_role_members R
ON R.[member_principal_id] =s.[principal_id]
WHERE S.[name] =@COPYDatabaseUserName'

How can I execute this againts all database, I tried using EXEC sp_MsForEachDb and am getting the following error message. @COPYDatabaseUserName variable is being used by other portion of the script too.

Must declare the scalar variable "@COPYDatabaseUserName".

nathans
Aged Yak Warrior

938 Posts

Posted - 2012-06-26 : 20:41:57

You need to declare your variable within the scope of the execute statement:


declare @command1 nvarchar(2000);
set @command1 = N'DECLARE @COPYDatabaseUserName [sysname]; SET @COPYDatabaseUserName = ''Domain\Laurine Torrone''; SELECT @CopyDatabaseUserName';
EXEC sp_MsForEachDb @command1;

or, construct the statement prior to executing. Sounds like you might want this option if the variable is needed further in the script:


DECLARE @COPYDatabaseUserName [sysname]
SET @COPYDatabaseUserName = 'Domain\Laurine Torrone';

declare @cmd nvarchar(2000);
set @cmd = 'SELECT 
S.[name],
S.[principal_id],
R.[role_principal_id],
''ROLE'' AS [role_name],
S.[default_database_name],
S.[default_language_name]
FROM ?.sys.server_principals S
INNER JOIN ?.sys.server_role_members R
ON R.[member_principal_id] =s.[principal_id]
WHERE S.[name] = ' + quotename(@COPYDatabaseUserName, '''');

print @cmd
EXEC sp_MsForEachDb @cmd;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources