Sysadmin role question

[OldSkoolSkater]

Hi All

Running Sql 2008 R2 Standard.
I have a SQL auth user account that is a member of the sysadmin server role and that allows me to create logins/users manually as you would expect.
The problem I am having is when I try to automate this process and run a stored procedure to do the same this - the SP fails with the following error:

CREATE LOGIN [test1] WITH PASSWORD = N'wibble',DEFAULT_DATABASE=[Erpmap], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.
CREATE USER [test1] FOR LOGIN [test1];
Msg 15007, Level 16, State 1, Line 1
'test1' is not a valid login or you do not have permission.
EXEC sp_addrolemember N'VFF_Permissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.

The SP that my sysadmin user is running is as follows:

USE [SM_Test]
GO
/****** Object: StoredProcedure [dbo].[pr_CallBoostedSecurityProcess] Script Date: 07/11/2013 10:40:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pr_CallBoostedSecurityProcess]( @name varchar(20), @password varchar(10) )
WITH EXECUTE AS 'SProcNinja'
--this is a sysadmin login and also database user
--(only database users can be used with EXECUTE AS clause in a proc),
--which has been disabled, but is used for elevated permissions.
AS
BEGIN
--CREATE a login, if required.
DECLARE @cmd varchar(max)
IF NOT EXISTS(SELECT 1 FROM master.sys.server_principals WHERE name = @name)
BEGIN
SELECT @cmd = 'CREATE LOGIN ' + QUOTENAME(@name)
+ ' WITH PASSWORD = N'''
+ @password
+ ''',DEFAULT_DATABASE=[Erpmap], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create Our role with specific permissions, if required.
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'DATABASE_ROLE' and name = 'VFF_Permissions')
BEGIN
SELECT @cmd = 'CREATE ROLE VFF_Permissions;'
PRINT @cmd
EXECUTE(@cmd)

SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''VFF_Permissions'';'
PRINT @cmd
EXECUTE(@cmd)

SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''VFF_Permissions'';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
--Create our user, if required
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE type_desc = 'SQL_USER' and name = @name)
BEGIN
SELECT @cmd = 'CREATE USER ' + QUOTENAME(@name) + ' FOR LOGIN ' + QUOTENAME(@name) + ';'
PRINT @cmd
EXECUTE(@cmd)
--assign the role to our user
SELECT @cmd ='EXEC sp_addrolemember N''VFF_Permissions'', N''' + @name + ''';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
END --PROC


Any ideas why this might be happening? I'm well confused!

Cheers

Oldskool - Living the dream in the UK

 

[SQLScholar]

The top of the proc says to execute as

'SProcNinja'

So does this user have the correct rights - either that or remove this line so it would then run as you.

BTW i havent actually read through all the procedure, this is just what stood out for me.

Dan

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[OldSkoolSkater]

Thanks

SProcNinja is the elevated user that has the sysadmin role so this should work IMO.
This SP is to allow basic support users to create accounts without giving the permissions to their accounts, the elevated user in the SP does al the hard work

Cheers

Oldskool - Living the dream in the UK

 

[yelworcm]

When you execute as a database user, you only gain the rights that user has on the database itself. You do not gain any of that user's server rights, or rights to other databases.

If you only want to create a login and a user, why not simply add a SQL Authenticated user to the SecurityAdmin server role? To create the user, you would need to grant that user at least the ALTER ANY USER privilege in the target database.

 

[OldSkoolSkater]

The SQL authenticated user I am using to execute the SP is a member of the sysadmin server role and also a member of the DB_owner database role to there are sufficent permissions aplenty, and this allows me to create logins and users when conecting as that user.

Strangely enough when connected as this elevated user, executing the SP with an explicit user_name (WITH EXECUTE AS 'SProcNinja') fails, but when I amend the SP to use (WITH EXECUTE AS CALLER) instead it works and the new target db user is created??

Why would it not work when using an explicit user_name but works with CALLER - it's the same user!

Cheers

Oldskool - Living the dream in the UK

 

[OldSkoolSkater]

Just re-read my post - Bad grammar, new version below:

The SQL authenticated user I am using to execute the SP is a member of the sysadmin server role and also a member of the DB_owner database role so there are sufficent permissions aplenty, and this allows me to create manually logins and target db users when connecting as that user.

Strangely enough when connected as this elevated user, executing the SP with an explicit user_name (WITH EXECUTE AS 'SProcNinja') fails, but when I amend the SP to use (WITH EXECUTE AS CALLER) it works and the new target db user is created??

Why would it not work when using an explicit user_name but works with CALLER - it's the same user!

Cheers

Oldskool - Living the dream in the UK