OK, I've tweaked the elevated perms SP script as follows:
USE [SM_Test]
GO
/****** Object: StoredProcedure [dbo].[pr_CallBoostedSecurityProcess] Script Date: 10/07/2013 14:27:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[pr_CallBoostedSecurityProcess]( @name varchar(20), @password varchar(10) )
WITH EXECUTE AS 'Obewan'
--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 = ''' + @password +''','
+ ' DEFAULT_DATABASE=[SM_Test], 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 = 'MyStandardPermissions')
BEGIN
SELECT @cmd = 'CREATE ROLE MyStandardPermissions;'
PRINT @cmd
EXECUTE(@cmd)
SELECT @cmd ='EXEC sp_addrolemember N''db_datareader'', N''MyStandardPermissions'';'
PRINT @cmd
EXECUTE(@cmd)
SELECT @cmd ='EXEC sp_addrolemember N''db_datawriter'', N''MyStandardPermissions'';'
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''MyStandardPermissions'', N''' + @name + ''';'
PRINT @cmd
EXECUTE(@cmd)
END --IF
END --PROC
I've created the SQL login 'obewan' and added it to the sysadmin server role, but when I run the SP I get the following error:
CREATE LOGIN [test1] WITH PASSWORD = 'test', DEFAULT_DATABASE=[SM_Test], 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'MyStandardPermissions', N'test1';
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'test1' does not exist in this database.
Obewan has also been granted explicit perms to run this SP but should not need it due to it being a sysadmin anyway??
Any ideas folks?