problem with EXECUTE AS statement

Question

Hi all,

I have created stored procedure:

CREATE PROCEDURE [dbo].[CreateLogin]  
	@username sysname, @pwd nvarchar(20)
WITH EXECUTE AS 'manager'
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @SQL NVARCHAR(300);
	
	SET @SQL = 'USE MASTER; CREATE LOGIN ' + @username + ' WITH PASSWORD = ''' + @pwd + ''', DEFAULT_DATABASE=[CallData], DEFAULT_LANGUAGE=[ENGLISH], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
	EXECUTE(@SQL);
	SET @SQL = 'USE CALLDATA; CREATE USER ' + @username + ' FOR LOGIN ' + @username;
	EXECUTE(@SQL);
	EXEC sp_addrolemember 'db_datareader', @username
	EXEC sp_addrolemember 'db_datawriter', @username

END;

Have created login 'manager' for the sql server and user 'manager' for database CallData. when I fire up the t-sql as a statement (not as a sp) I can create the login and user without any problem, but cannot fire (even as a 'sa') the stored procedure getting error message:

Msg 15247, Level 16, State 1, Line 1
User does not have permission to perform this action.

when I remove from the sp the line:

WITH EXECUTE AS 'manager'

there is no problem to fire the sp. Any sugestion?

Answer 1

Those statements are not exactly SELECT queries in the sp!

For example, CREATE LOGIN requires:  ALTER ANY LOGIN permission on the
server or membership in the securityadmin fixed server role.

What is the role membership of login manager? User manager?

BOL article: "EXECUTE AS Clause (Transact-SQL)

In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.

....

USER
Specifies the context to be impersonated is a user in the current database. The scope of impersonation is restricted to the current database. A context switch to a database user does not inherit the server-level permissions of that user.

 Important 
While the context switch to the database user is active, any attempt to access resources outside of the database will cause the statement to fail. This includes USE database statements, distributed queries, and queries that reference another database that uses three- or four-part identifiers. To extend the scope of the context switch outside the current database, see Extending Database Impersonation by Using EXECUTE AS. "

http://msdn.microsoft.com/en-us/library/ms188354.aspx

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 2

This is because with the EXECUTE AS clause you are impersonating a database user, in which case you are sandboxed into the cucrrent database.

There are ways to work around that, but they have security implications. Or you could use certificate signing which is much better.

Read this article on my web site:
http://www.sommarskog.se/grantperm.html
It's long, but it will tell you everything you need to know.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

the server role for login manager is 'public' and 'securityadmin'

the database role for user manager is 'db_owner'

Answer 4

the best ever answer and understandable explanation is there:

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/24/how-to-impersonate-the-privileges-to-create-a-login-using-the-stored-procedures-using-execute-as-clause.aspx

thank you very much to all who tried help