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