how to allow non admin users to manage security using stored procedures using execute as and sp_executesql

Question

  I´m building a windows application that uses sql authentication for its users. Now I need to build the functionality needed to manage application users and roles, that leverages in sql users (logins) and roles.

  My first step was to build a procedure for adding a new user to the system (by creating a new login and granting dbaccess).
  The problem is executing admin functions as CREATE LOGIN with a non admin user. 
  In the article How to impersonate the privileges to create a login using the Stored procedures using “EXECUTE AS” clause, a partial solution is shown.
  I say partial because in this solution a non admin user is granted to impersonate an admin user. Unfortunatelly this situation is a security risk because it´s possible to do impersonation outside the application or the application security procedures, thus performing any king of action without control.

  Thanks in advance.

  DR.

Answer 1

To execute an admin function with a non admin user you may try the following

Create procedure procedurename with execute as 'dbo'
as
begin
...
end

Then enable Trustworthy option on master database using the following statement

Alter database master set TRUSTWORTHY on

Then grant the EXECUTE privilege to the user(non admin) on this newly created stored procedure

Hope this helps
Feroz

---

Mark as Answer if it helps. This posting is provided "AS IS" with no warranties and confers no rights.

Answer 2

How about making use of SECURITYADMIN server role for executing CREATE LOGIN statement without admin access ?

---

Thanks, Leks

Answer 3

Thanks I will try it right now!

Answer 4

No, i don´t want to do that since the user can login outside the application and create logins without control.
IE: the application logins are created using a prefix.

Thanks!

DR

Answer 5

It worked!

NOTE: the alter database must apply to de database containig the procedure that performs the create login.

Thanks a lot!

DR.