June 21, 2012 at 2:16 pm
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".
June 21, 2012 at 2:45 pm
Try this:
EXEC sp_MsForEachDb @command1 =
'DECLARE @COPYDatabaseUserName [sysname]
SET @COPYDatabaseUserName = ''Domain\Laurine Torrone''
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'
June 22, 2012 at 2:56 am
This should do the trick:
DECLARE @COPYDatabaseUserName [sysname]
SET @COPYDatabaseUserName = 'Domain\Laurine Torrone'
DECLARE @statement nvarchar(max);
DECLARE @sql nvarchar(max);
DECLARE @results TABLE (
DatabaseName sysname,
name sysname,
principal_id int,
role_principal_id int,
role_name char(4),
default_database_name sysname,
default_language_name sysname
)
SET @statement = '
SELECT
DB_NAME() AS DatabaseName,
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'
-- Escape single quotes
SET @statement = REPLACE(@statement, '''', '''''')
-- Remove line terminators
SET @statement = REPLACE(@statement, char(10), space(0))
SET @statement = REPLACE(@statement, char(13), space(0))
-- Surround with quotes
SET @statement = 'N''' + @statement + ''''
;WITH dbs AS (
SELECT *
FROM sys.databases
WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0
AND HAS_DBACCESS(name) = 1
AND state_desc = 'ONLINE'
)
SELECT @sql = (
SELECT
'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql ' +
@statement + ',' +
'N''@COPYDatabaseUserName sysname'',' +
'@COPYDatabaseUserName;' AS [text()]
FROM dbs
ORDER BY name
FOR XML PATH('')
)
INSERT @results
EXEC sp_executeSQL @sql, N'@COPYDatabaseUserName nvarchar(max)', @COPYDatabaseUserName
SELECT *
FROM @results
Hope this helps
Gianluca
-- Gianluca Sartori
June 25, 2012 at 10:42 am
Don't be surprised if some databases, sometimes will be missed by sp_MsForEachDb...
June 27, 2012 at 1:45 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply