How to impersonate the privileges to create a login using the Stored procedures using “EXECUTE AS” clause

As most of the times we do it in a simple way descirbed below. but when we try to do the same through stored procedure it fails.

Just for information regarding impersonation:- https://msdn.microsoft.com/en-us/library/ms188354(SQL.90).aspx

Solution:

This is how we normally use “EXECUTE AS” caluse:

The main intent to is to show how to create a login, using a login, which doesn’t have any permission to create a login.

Most simple way to do this:

Login_name1:- The user which without securityadmin role.

Login_Name2:-The user with securityadmin role.

1. Login into query analyzer with the user having securityadmin role i.e. using Login_Name2 .

            GRANT IMPERSONATE ON Login::<Login_name2> TO <Login_name1>;

2. Now Login into query analyzer with the user without securityadmin role. i.e. using Login_Name1.

EXECUTE AS login= ’<Login_name1>’

Create login test with password = 'test', default_database= master,check_expiration= off,check_policy=off

It seems to be quite simple. isn’t it?

But it becomes complex when we try to do it using stored procedure without granting additional rights to the users . This is helpful while creating login though application.

Here is how to achieve this using Stored procedure.

Login into the query analyzer using security admin privilege login :- <Login_name2>

GRANT IMPERSONATE ON Login::<Login_name2> TO <Login_name1>;

Create a stored procedure:

use <User DB>

go

create proc dbo. sp_createlogin @szLogin varchar(50),@szpassword varchar(50), @defaultdb varchar(50)

with execute as ‘<Login_name2>’

as

if not exists ( select * from sys.sql_logins where name = @szlogin)

begin 

  exec ('create login [' + @szLogin + '] with password = ''' + @szpassword +''',default_database='+@defaultdb + ',check_expiration=off,check_policy=off')

end 

return 0

Grant EXECUTE privilege to the user who would be executing this stored procedure.

1. Login into SQL server using the <Login_name1>

2. Execute the stored procedure:

Exec sp_createlogin ‘testuser’, ‘testuser’ , ‘testdb’

When we execute the stored procedure it fails with an error:

Msg 15247, Level 16, State 1, Line 1

User does not have permission to perform this action

Let us break it down in execution steps of this stored procedure.

EXECUTE AS User=‘<Login_name2>’;

Create login test with password = 'test', default_database=master,check_expiration=off,check_policy=off

The above is what logically we are trying to do using stored procedure.

The above fails because the stored procedure executes as below:

EXECUTE AS User='test';

Users do not have server level privileges to create a login and it fails. Below diagram shows the securable information and actionables.

Example:

========

Below example which can be invoked in application to create login at runtime.

1. Create test1 with security admin privileges.

2. Create test2 (Public user).

1. Login as test1.

2. GRANT IMPERSONATE ON Login::test1 TO test2;

3. Create this Stored procedure with login have privileges to create PROC.

Use <UserDB>

go

create proc [dbo].[SP_createlogin] @szLogin varchar(50),@szpassword varchar(50), @defaultdb varchar(50)

as

DECLARE @A VARCHAR(30);

if not exists ( select 0 from sys.sql_logins where name = @szlogin)

begin

EXECUTE AS LOGIN='test1';

print 'EXECUTE AS LOGIN= '+'''test1''';

exec ('create login [' + @szLogin + '] with password = ''' + @szpassword +''',default_database='+@defaultdb + ',check_expiration=off,check_policy=off')

REVERT;

print 'p'

end

4. Grant EXECUTE privileges to test2

Grant execute on [dbo].[SP_createlogin] to test2

5. Login into new query analyzer using test2 login and Execute the procedure :

Exec USERDB.. sp_createlogin ‘testuser’, ‘testuser’ ,’defaultdb’

It will succeed without any errors.

Harsh Chawla,
SE, Microsoft Sql Server

Reviewed By

Nickson Dicson
Technical lead, Microsoft Sql Server