Tuesday, 28 August 2012

Changing Database context inside store procedure

When I was developing a code today  I landed in a situation to create a store proc on database DB1 which inturn needs to create user and schema on database DB2  and also grant roles on DB2, with the below code

Since we know USE DB statement is not allowed in a store procedure, I decided to use as below and tried to execute the query,
 
set @sql = 'USE DB2;CREATE USER ['+@sName + '] FOR LOGIN ['+ @sName +']'
exec(@sql)
--give the user DB_reader/db_dbwriter rights
set @sql = 'USE DB2;sp_addrolemember ''db_datareader'', '+ @sName +''
exec(@sql)
set @sql = 'USE DB2;sp_addrolemember ''db_datawriter'', '+ @sName +''
exec(@sql)
-- create the schema
set @sql = 'USE DB2;CREATE SCHEMA '+ @sName +' AUTHORIZATION '+ @sName
exec(@sql)
-- set the user schema to be the new schema
set @sql = 'USE DB2;ALTER USER ['+ @sName +' ] WITH DEFAULT_SCHEMA=[' + @sName + ']'
exec(@sql)
 
which resulted with the following error,
 
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_addrolemember'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_addrolemember'.
Msg 111, Level 15, State 1, Line 1
'CREATE SCHEMA' must be the first statement in a query batch.
 
So I was not able to use USE DB2 in EXEC statement too, In SQL 2012 I can use
 
USE DB2;ALTER ROLE db_datareader ADD MEMBER test
USE DB2;ALTER ROLE db_datawriter ADD MEMBER test
 
which will resolve the sp_addrolemember syntax error, but CREATE SCHEMA error will not resolve as the statement will need to be the first statement in the query batch. Finally after considering various options this is how I overcame the issue,
 
1. create a Store proc usp_createuser on DB2 with no DB context with the above code.
2. Added the below code to the parent store procedure usp_security
   Exec DB2.dbo.usp_createuser @parameter1,@parameter2
 
which resolved my issue, I am fine with this resolution as of now but we have system with 200 Databases and I don't want to create 200 SP to overcome this problem, I shall blog if I find a better answer to this.

1 comment: