Post reply

dbcreator server role has permission to change sa password?

  • November 14, 2011 at 9:25 am

    #246835

    i'm trying to create a remote login to the vendor who would need to deploy the database application to our site (client).

    vendor requested to have remote login to the sql server 2008 and atleast a dbcreator role. i created the user with dbcreator role, and i see that this user has permissions to view Security of the SQL server, and can change the "sa" password.

    how can i restrict that this dbcreator user cannot see or change the sa password? is this possible in SQL server 2008 R2?

    Thanks!

  • November 14, 2011 at 9:30 am

    #1407313

    Someone who solely has the db_creator role does not have the rights to change the sa password (and there's no mechanism at all to see passwords, they're stored hashed)

    I created a login, db_creator permissions only and tried:

    ALTER LOGIN sa with PASSWORD = 'Test'

    Msg 15151, Level 16, State 1, Line 1

    Cannot alter the login 'sa', because it does not exist or you do not have permission.

    Check that they guy isn't a member of sysadmins, doesn't have control server and, if it's a windows login, check that it's not part of a group that has any of those permissions.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2011 at 9:43 am

    #1407337

    this user is a brand new user that i created on the sql server and is not the windows user. I created a brand new sql user on the server, and granted the dbcreator role. he has dbcreator, and public properties checked in the Server roles.

    i didn't try changing the password for sa after i logged in as dbcreator user. but i am definitly able to see the reset password screen for sa when i logged in as dbcreator user. here are the steps to the what i'm seeing...

    I launched sql server --> logged in as dbcreator user --> select Secuity tab from the object Explorer --> select Logins --> i can see the dbcreator user what i'm logged in as, and also the sa user here --> select sa and double click is giving the Login Properties window where it allows to reset the password and change setting and OK button is enabled. i didn't test and didn't want to try changing the sa password.

  • November 14, 2011 at 10:21 am

    #1407383

    Able to see the screen != able to change the password.

    A member of db_creator cannot change the password. If he can get the dialog up then that's all he'll be able to do. Actually changing it will throw an error (test on a local instance if you want)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 14, 2011 at 10:47 am

    #1407415

    Thanks!!! yes, i tried it and i got the exception error, and dbcreator cannot change the sa password. that's good!

    Thanks a lot!

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

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