August 31, 2010 at 6:51 am
I'm trying to pass a variable into the use database statment but can't get it to work.
My code is comething like the below
DECLARE @DatabaseName Nvarchar(30)
DECLARE @RowCount int, @RowNumber int
CREATE TABLE #Databases
(
RowID int IDENTITY(1,1),
DatabaseName Nvarchar(50)
)
INSERT INTO #Databases(DatabaseName)
(
Select Name
FROM sys.databases
)
SET @RowNumber = @@RowCount
SET @RowCount = 1
WHILE @RowCount <= @RowNumber
BEGIN
SET @databasename = (SELECT databasename
FROM #databases
WHERE ROWID = @RowCount
)
SET @sql = 'USE ['+@DatabaseName+']
GO
SET NOCOUNT ON
SELECT @@ServerName,
'''+@databaseName+''',
rm.member_principal_name,
rm.principal_type_desc,
rm.role_name,
Getdate()
FROM sys.database_principals p
right outer JOIN (
select role_principal_id,
dp.type_desc as principal_type_desc,
member_principal_id,user_name(member_principal_id) as member_principal_name,
user_name(role_principal_id) as role_name
from sys.database_role_members rm
INNER JOIN sys.database_principals dp
ON rm.member_principal_id = dp.principal_id
) rm
ON rm.role_principal_id = p.principal_id
Where principal_type_desc in (''Sql_User'',''WINDOWS_USER'',''WINDOWS_GROUP'')'
execute(@SQL)
SET @RowCount = @RowCount + 1
END
DROP TABLE #Databases
But I'm getting an syntax error. If I print @sql and copy and paste it into a new query it runs fine.
August 31, 2010 at 7:05 am
I Jumped the gun a little with the post, I've managed to use EXEC sp_MSForEachDB to achive the results I wanted.
Dave
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply