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?