July 22, 2011 at 8:34 am
My code fails with the error 'Must declare the scalar variable "@OldUser".'
PLZ let me know what has to be changed.
IF OBJECT_ID('dba..Accessscript') IS NOT NULL
BEGIN
DROP TABLE dba..Accessscript
END
CREATE TABLE [dba].[dbo].[Accessscript](
[databasename] [nvarchar](128) NULL,
[olduser] [sysname] NULL,
[newuser] [sysname] NULL,
[Comment] [nvarchar](545) NULL,
[RoleMemberships] [nvarchar](566) NULL,
[ObjectLevelPermissions] [nvarchar](1250) NULL,
[DatabaseLevelPermissions] [nvarchar](470) NULL
) ON [PRIMARY]
DECLARE @OldUser varchar (100)
DECLARE @accessscript varchar (4000)
DECLARE@newuser varchar (100)
DECLARE @getloginName CURSOR
SET @getloginName = CURSOR FOR
SELECT loginname
FROM [dba].[dbo].[Scilogins]
OPEN @getloginName
FETCH NEXT
FROM @getloginName INTO @OldUser
WHILE @@FETCH_STATUS = 0
BEGIN
SET@newuser = stuff(@OldUser,1,3,'corp')
SET NOCOUNT ON
set @accessscript =
'use [?];
Insert into dba..Accessscript (databasename,olduser,newuser,comment,RoleMemberships)
SELECT db_name(),@OldUser,@NewUser,
''Cloning permissions from'' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + ''to'' + SPACE(1) + QUOTENAME(@NewUser)as ''comment'',
''EXEC sp_addrolemember @rolename ='' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + '',@membername ='' + SPACE(1) + QUOTENAME(@NewUser, '''') AS ''RoleMemberships''
FROMsys.database_role_members AS rm
WHEREUSER_NAME(rm.member_principal_id) = @OldUser
ORDER BY rm.role_principal_id ASC
Insert into dba..Accessscript (databasename,olduser,newuser,ObjectLevelPermissions)
SELECTdb_name(),@OldUser,@NewUser,CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
+ SPACE(1) + perm.permission_name + SPACE(1) + ''ON '' + QUOTENAME(USER_NAME(obj.schema_id)) + ''.'' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE ''('' + QUOTENAME(cl.name) + '')'' END
+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END AS ''ObjectLevelPermissions''
FROMsys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
WHEREusr.name = @OldUser
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Insert into dba..Accessscript (databasename,olduser,newuser,DatabaseLevelPermissions)
SELECTdb_name(),@OldUser ,@NewUser,CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END
+ SPACE(1) + perm.permission_name + SPACE(1)
+ SPACE(1) + ''TO'' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default
+ CASE WHEN perm.state <> ''W'' THEN SPACE(0) ELSE SPACE(1) + ''WITH GRANT OPTION'' END AS ''DatabaseLevelPermissions''
FROM sys.database_permissions AS perm
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
WHEREusr.name = @OldUser
ANDperm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC
'
exec sp_msforeachdb @accessscript
FETCH NEXT
FROM @getloginName INTO @OldUser
END
CLOSE @getloginName
DEALLOCATE @getloginName
select * from dba..Accessscript
July 22, 2011 at 8:48 am
The variables that are declared outside of the dynamic SQL are not recognized inside the dynamic SQL, so when you run the dynamic SQL and reference those variables, you get the error message.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 22, 2011 at 8:53 am
How to get this corrected?
July 22, 2011 at 9:07 am
Break the string and insert the value of @oldUser.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply