Executing Stored Procedure with elevated premissions RRS feed

  • Question

  • I am the dba of my server and I have created a stored procedure in the master db.  This stored procedure is basically a full create db script (including security, tables etc....). 

    The reason I have done this is becuase we have a small team of three people a dba and two developers that come together on adverse type of events (I work in healthcare).  I want to give my two developers the ability to create a db using this stored procedure as I may be unavailable.  They have no server roles other than pubic and I don't wish to give them anymore. 

    I have tried to add 'with execute as owner' within my stored procedure, and have given the developers execute permissions on the sp.

    The database acutually gets created with no issues but once I it tries to create any objects it gives me the error ;

    The server principal "sa" is not able to access the database "databasename" under the current security context.

    There is a LOT of dynamic sql within the code and I have tried a lot of things to get this to work for a user with only public server roles but with no luck....  Also I am not stuck with doing it this way, is there another way in which this should be done?  I understand the risks of execute as...

    Thanks again,


    Thursday, July 22, 2010 11:19 PM

All replies