How to change database owner for login and database created inside stored procedure?

Question

I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my stored procedure creates the new login and new database but will not assign the new user as the owner of the new db. The stored procedure is in an application database and not master.

The error I get is:

Cannot find the principal 'BlahUser', because it does not exist or you do not have permission.

The stored procedure is executed under a different login (simpleUser) with limited rights (execute on stored procedure).

The certificate user has the following permissions:

• create any database
• alter any login

The code in question is :

SET @sqlStmt = 'ALTER AUTHORIZATION ON DATABASE::'+@sessionid+'Data TO ['+@orgName+'User]'
SET @metasql = '
USE [master]
EXEC (''' + REPLACE(@sqlStmt, '''', '''''') + ''')
'
EXEC sp_executesql @metasql

I tried granting impersonate from the executing user (simpleUser) to the new user inside the stored procedure like this, but it also results in an error. I'm not sure if users who create other users can automatically impersonate them.

SET @sqlStmt = 'GRANT IMPERSONATE ON LOGIN::'+@orgName+'User TO simpleUser';
SET @metasql = '
USE [master]
EXEC (''' + REPLACE(@sqlStmt, '''', '''''') + ''')
' 
EXEC sp_executesql @metasql

• A-K: Typically creating a database is not a frequent task. As such, most shops do not automate it and do not need to create databases via stored procedures. Can you just create your database from a script?

Answer 1

The way that works best for me, to write stored procedures that execute dynamic SQL, is by not using "EXEC sp_executesql" when I start writing the stored procedure, but by using "PRINT" statements.

I make sure I get to see each and every SQL that the stored procedure generates. I copy them from the Messages tab and execute them from within Management Studio. Only after all SQLs work as intended, I allow my stored procedure to "EXEC sp_executesql" (instead of simply PRINTing the SQL statements).

Debugging works very fast this way. The most problems I encountered so far, are the result of missing spaces, like 'FROMMyTableName' or 'WHEREId = 12'.

You will have to test this as the certificate user or with an account that has similar rights. And if you can avoid using dynamic SQLs, avoid them.