none
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?
    Monday, March 12, 2012 9:52 PM

Answers

All replies