USE Statement with variable
Posted on 2007-11-22
i am trying to script the creation of a user and add it to a database like this:
declare @DbAttachName varchar(50);
SET @DbAttachName = 'test_script';
CREATE USER [nvt] FOR LOGIN [nvt] WITH DEFAULT_SCHEMA=[dbo];
EXEC sp_addrolemember @rolename='db_owner',@membername='nvt';
the login was created succesfully, no problem there.
on the line with the ## there is an error. apparently the USE statement does not support variables.
when i try like this:
SET @SQL='USE ' + @DbAttachName;
I get no error, but the database does not change...
i suppose it only changes the database in the "EXEC" statement, and not in the script itself.
How can i solve this?
The problem is, that the database is also attached in the same script (not when testing this)
so i can not fix the USE name.