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 SProc creates the new login and new database but will not assign the new user as the owner of the new db. The SProc is in an application database and not master.
The error I receive is: Cannot find the principal 'BlahUser', because it does not exist or you do not have permission. The SProc is executed under a different login (simpleUser) with limited rights (execute on SProc).
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