I am using Microsoft's Server Management Studio 2012 for this, along with their SQL Server 2008. I am attempting to take a role (CustomerRole) and get all the users in that role, put the users into a table variable (@Roles) and use the MemberName "column" to store their logins. Ultimately I will be using this information to drop the users, their logins and eventually the role completely.
CREATE PROC dropLogins
AS
DECLARE @Roles AS Table(DbRole varchar(20), MemberName varchar(50), MemberSID varchar(50))
INSERT INTO @Roles
EXEC sp_HelpRoleMember CustomerRole
DECLARE Member_Cursor CURSOR
DYNAMIC
FOR
SELECT DISTINCT MemberName
FROM @Roles
EXEC sp_HelpRoleMember CustomerRole
OPEN Member_Cursor
FETCH NEXT FROM Member_Cursor
INTO @Roles --NOT DECLARED*
WHILE @@FETCH_STATUS = 0
ALTER ROLE CustomerRole DROP MEMBER MemberName
FETCH NEXT FROM Member_Cursor
INTO @Roles --NOT DECLARED*
CLOSE Member_Cursor
DEALLOCATE Member_Cursor
The problem occurs at the areas that state --NOT DECLARED*. I receive the "Must declare the scalar variable "@Roles"." error in those 2 regions, though I'm not sure how I can get around this error. I understand that the error is most likely caused by the code being split up into 2 and 3 batches, though I'm unsure of how to prevent this. Any help is very much appreciated. Thank you!
1 Answer
You are creating a table variable called @roles.
Within the cursor, you return a field, I assume is varchar(). You cannot take the output of the cursor (a varchar field) and assign it into a table variable, they are different types
You could do
DECLARE @theRole VARCHAR(200)
FETCH NEXT FROM Member_Cursor into @theRole
INSERT INTO @roles (dbrole) VALUES (@theRole)
Or something similar
