Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Mar 6 '12 at 18:30

This question came from our site for professional and enthusiast programmers.

    
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? –  AlexKuznetsov Apr 5 '12 at 20:07

1 Answer 1

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

I make sure I get to see each and every SQL that the sproc 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 sproc 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.