I have a SQL script that needs to change from a particular DB to the Master DB (to grant EXECUTE permissions to a few SPs in the Master DB), then change back to the original DB. I will not know at design time what the DB name is.
What I am trying to do is something like this:
Grant EXECUTE on sp_OACreate TO C2SRolePowerUsers
Declare @DBName varchar(32)
EXECUTE xp_instance_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\ODBC\ODBC.ini\MyDSNName', 'Database', @param = @DBName OUTPUT
1) I have to do this in a single pass using ADO and the script. The reason is that it is part of a generic routine, looping through a list off commands, and cannot perform one command, change the DB reference via ADO, then change back in separate ADO commands.
2) It fails on the second USE command.
I have looked to see if there is some sort of "EVAL()" function, or a stored procedure to change the current DB. No luck.
This just gets me the current database name (which would be Master). If executed to get the DB name before changing to Master (such as by "Set @DBName = DB_NAME()"), the GO command wipes out the variable. Regardless, it doesn't change the current selected DB. Thanks anyway.
Originally Posted by Pat Phelan
Granted this is somewhat deviant, but:
EXECUTE ('USE Master GRANT EXECUTE ON sp_OACreate TO C2SRolePowerUsers')