create logins without being in the securityadmin role

Question

create logins without being in the securityadmin role

ricfors

Say Hey Kid

Points: 665
More actions
October 2, 2007 at 8:13 am

#92739

Hi,
I need to give a user the ability to create logins but don't want to add him to the securityadmin role.
Is there a way to grant those permissions separately?
Or simply can a user be allowed to create logins without being a member of the securityadmin role?
If so, how do I do?
Thanks in advance
Ricardo

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Strommy SSCrazy Points: 2386 More actions · Answer 1

Hi Ricardo,

You can certainly do this using T-SQL. Just for a point of reference, under Enterprise admin, go to security -> Server Roles -> security admin. Right-click and choose properties on this role. This is a listing of all the things this role can do. You could grant a user a small subset of this, such as sp_grantlogin, sp_addlogin. THis would let them add both Windows and SQL logins and nothing else. This may be a problem if they want to use the GUI (EM) rather than query analyzer, since the gui runs several commands in the background. The good thing about looking at the security admin role is that you can pick and choose what you want this user to be able to do.

Another option is to grant them this role and choose what you don't want them to do. For example, take away their ability to do linked server logins:

"deny exec on sp_addlinkedsrvlogin to TestAccount"

Either method should work fine, depending on how restrictive you want to be.

THanks,

Eric

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

You could try granting INSERT permission to sysxlogins, but I don't know if that will work or if it's a good idea. Creating logins wouldn't do much good if the new logins aren't granted database access also. If you don't trust the user to do the other functions of securityadmin, maybe he shouldn't add logins either.

Greg

Strommy SSCrazy Points: 2386 More actions · Answer 3

Greg has a couple good points. First off, there is a difference between granting database access and granting login access. In theory, though, a user that can add logins should only be adding database access to a database that they also have access to.

As for adding sp_addlogin, you do have to allow the user to have r/w access to whatever table you are granting them access to. If you go the deny route, however, you probably don't have to do that additional work.

Thanks,

Eric

ricfors Say Hey Kid Points: 665 More actions · Answer 4

Thanks for your contributions!

On your suggestion, Strommy, how do I deny the 3 first permissions that are shown in the properties for security admin?

- Add member to security admin

- grant/deny/revoke CREATE DATABASE

- Read the error log

The stored procedures are easy and obvious, but not these ones?

Thanks,

ricardo

Strommy SSCrazy Points: 2386 More actions · Answer 5

For reading the error log, deny exec on master..sp_readerrorlog. Looking into the others...

Thanks,

Eric

Strommy SSCrazy Points: 2386 More actions · Answer 6

As for adding users to the security admin role, revoke exec on sp_addsrvrolemember. I'm stumped on the grant/revoke/deny create database. I don't think that's possible. Anyone else have any thoughts?

Thanks,

Eric

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Ricardo (10/2/2007)
Or simply can a user be allowed to create logins without being a member of the securityadmin role?
If so, how do I do?

In SQL Server 2000, there is no explicit way to do so. In SQL Server 2005 there now is. However, looking at it from a SQL Server 2000 perspective, you've gotten a lot of workaround that require trying to put explicit denies in place. What may be simpler (and cleaner):

Give the user the ability to execute a stored procedure. Said stored procedure drops the login name into a table. A SQL Server Agent process runs every so often (say every five minutes), picks up the entries in the table, and creates the logins.

K. Brian Kelley
@kbriankelley

Strommy SSCrazy Points: 2386 More actions · Answer 8

I must say I am jealous. Mr. Kelly's solution is far easier and elegant. The only down-side is having to teach Enterprise Manager addicts how to use QA, or writing your own GUI front-end.

Thanks,

Eric

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 9

Strommy (10/3/2007)
I must say I am jealous. Mr. Kelly's solution is far easier and elegant. The only down-side is having to teach Enterprise Manager addicts how to use QA, or writing your own GUI front-end.

This is actually easy to do. Create a web application (only needs to be a single page, really). Secured using Windows authentication and NTFS permissions. Specify the context (if you're using ASP.NET) of a service account for the web application. The page can display the list of logins, give the user the ability to add a login, and then executes the login creation. If the service account has securityadmin rights, you've accomplished the same thing without giving direct access to the user.

K. Brian Kelley
@kbriankelley