dbcreator server role has permission to change sa password?

Question

dbcreator server role has permission to change sa password?

preetid2

Ten Centuries

Points: 1088
More actions
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!

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

preetid2 Ten Centuries Points: 1088 More actions · Answer 2

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

preetid2 Ten Centuries Points: 1088 More actions · Answer 4

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

Thanks a lot!