Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Login in SP - limited access and premissions problem Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2014 9:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:23 AM
Points: 77, Visits: 72
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?
Post #1572737
Posted Tuesday, May 20, 2014 1:35 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1572875
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse