Post reply

Create Login Only

  • November 21, 2013 at 10:34 am

    #279271

    How can you allow a user(login/server principal) to only CREATE other logins?

    I want to setup a user(server principal) to add new logins, and they then could add that login as a user of their DB. They are db_owner of their DB, but I don't want them to be able to alter or drop other logins.

    How would I go about doing this?

    Thanks for the help!

  • November 21, 2013 at 11:12 am

    #1668199

    I don't think you can do this. Security is a big deal, and this isn't a place where you want users to have permissions. I tried an "Execute as" stored proc, but it didn't seem to work. I will futz with it, but my understanding is that to create a login you need "ALTER ANY LOGIN" permissions.

    what you could do, and what I'd suggest, is you build a table where your particular user can "request" a login by putting a row in there. Then I'd build a sysadmin process that reads the table, creates the login and user, assigns a known password with "must change password" set (or maps to a Windows login) and then updates the table to mark the timestamp when the login was created.

    You could execute this process with SQLAgent every minute. Worst case, your user waits a couple minutes for a login.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • November 21, 2013 at 12:04 pm

    #1668214

    That is a fantastic idea!

    Thanks for the help!

    -Dane

  • November 22, 2013 at 8:55 am

    #1668451

    Alternately, you could create a stored procedure that creates users with whatever restrictions you like, create a certificate, sign the stored procedure with the certificate, create a user based on the certificate, assign that certificate based user permissions to create users, and assign the human you're talking about's account EXECUTE on the stored procedure.

    Here's a post with an example of certificate based security: http://qa.sqlservercentral.com/articles/Security/68873/[/url]

    WARNING: Certificate based security means you need to re-sign the stored procedure every time you change it!

  • November 22, 2013 at 9:00 am

    #1668454

    I will have to try that out in my test environment! Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

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