Create Login in SP - limited access and premissions problem

• Emil B

  SSCertifiable

  Points: 5568

  More actions

  May 20, 2014 at 9:09 am

  #308193

  Hi,

  i have an example stored procedure

  CREATE PROCEDURE [adm].[CreateTestUser]

  @login NVARCHAR(50),

  @Password NVARCHAR(50)

  AS

  DECLARE @SQL NVARCHAR(MAX)

  SET @SQL = 'CREATE LOGIN ['+@Login+'] WITH PASSWORD = '''+@Password+''', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'

  EXEC (@SQL)

  I'm getting an error 'User does not have permission to perform this action.' even if i add 'WITH EXECUTE AS OWNER'

  i want to run this procedure as User 'UserAdmin' which has EXECUTE premissions to schema adm and sys but not to dbo. I don't want my user to have too much access and do only what store procedure lets him to do.

  I did try to use impersonate but its not very secure, user can do EXEC('...') AS LOGIN = xxx and have the same access as impersonated login. If i will map him as securityadmin role he will be albe to skip the stored procedure and create users himself with any access.

  User is a member of crl_admin role. below how the role was set up

  CREATE SCHEMA adm

  CREATE ROLE crl_admin

  DENY VIEW DEFINITION ON SCHEMA::[dbo] TO [crl_admin];

  GRANT VIEW CHANGE TRACKING ON SCHEMA ::[dbo] TO [crl_admin];

  DENY SELECT ON SCHEMA ::[dbo] TO [crl_admin];

  GRANT SELECT ON SCHEMA ::[sys] TO [crl_admin];

  DENY VIEW DEFINITION ON SCHEMA::[sys] TO [crl_admin];

  GRANT EXECUTE ON SCHEMA::[adm] TO [crl_admin];

  GRANT VIEW CHANGE TRACKING ON SCHEMA ::[adm] TO [crl_admin];

  i'm lacking of ideas. Any suggestions?

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  May 20, 2014 at 1:35 pm

  #1715115

  Creating a LOGIN requires server-level permissions, so it doesn't really matter what permissions you grant within the database, even a user with dbo/db_owner permission won't be able to create a login.

  I'd be inclined to sign the procedure to grant it rights to create a login. You can read some about signing a procedure in these places:

  http://sommarskog.se/grantperm.html

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

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question