May 18, 2010 at 1:29 am
Hi, in our consolidated environment (SQL Server 2008 SP1 Ent) an application needs to create users by itself, is it possible to enable that user without giving it server-level role or alter any login (because if I give alter any login that application user will have been authorized to change all logins in the server), I tried execute as but it didn't work for create login case. Any other suggestions?
ALTER PROCEDURE sp_createLogin
-- Add the parameters for the stored procedure here
@login sysname,
@pass sysname
WITH EXECUTE AS 'dba'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
CREATE LOGIN login2 WITH PASSWORD='YYYYYY'
END
Even dba (impersonated user) and the calling user are sysadmin in my test, when I call the sp it gives:
Msg 15247, Level 16, State 1, Procedure sp_createLogin, Line 19
User does not have permission to perform this action.
Any suggestions?
Thanks,
Serter
May 18, 2010 at 6:52 am
I was doing something similar yesterday, my understanding is this ...
The user 'dba' will need to connected to a SQL login that has the rights to create server logins. I.e. whichever SQL login that is mapped to 'dba' must have this permission.
You then need to set the database where the procedure that is being exectuted to trustworthy
ALTER DATABASE yourdbname SET TRUSTWORTHY ON
The user will still need permission to execute the sproc that creates the user.
Hope this is of some help.
Cheers, Andy
May 19, 2010 at 11:44 am
Thanks Andy!!! This solved my problem, msdn says master database is set to TRUSTWORTHY OFF by default, when I set the option it worked, thanks again,
Cheers Serter
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply