July 22, 2014 at 7:10 am
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]
GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[sp_create_db_users]
with execute as 'Mydomain\admin1' as
declare @Cmd Varchar(5000)
set @Cmd =
'
USE [MYDATABASE]
if not exists (select name from sys.database_principals where name = "Mydomain\mygroup)
begin
CREATE USER [Mydomain\mygroup] FOR LOGIN [Mydomain\mygroup]
end
ALTER ROLE [db_owner] ADD MEMBER [Mydomain\mygroup]
'
--print @Cmd
exec (@Cmd)
GO
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.
July 23, 2014 at 11:41 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply