Allow a low privilege user to execute a stored procedure to create db users

  • I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.

    The database restore works ok by placing the user in the dbcreator role.

    There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.

    USE [master]


    /****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/





    CREATE procedure [dbo].[sp_create_db_users]

    with execute as 'Mydomain\admin1' as

    declare @Cmd Varchar(5000)

    set @Cmd =



    if not exists (select name from sys.database_principals where name = "Mydomain\mygroup)


    CREATE USER [Mydomain\mygroup] FOR LOGIN [Mydomain\mygroup]


    ALTER ROLE [db_owner] ADD MEMBER [Mydomain\mygroup]


    --print @Cmd

    exec (@Cmd)


    The user has execute permission on the stored procedure but keeps getting the error:

    Msg 916, Level 14, State 1, Line 2

    The server principal "Mydomain\admin1" is not able to access the database "Mydatabase" under the current security context.

    Mydomain\admin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomain\admin1 it works so the issue is between the execute sp and the actual running of the procedure.

    Is this an issue of impersonation?

    What is going wrong and if there is a better method please let me know.

  • Change the execute as principal from the domain user to dbo.

    Alternatively, you can grant the alter any role permission in the database, and he can add users to any role in the database he/she likes.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply