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.