June 3, 2008 at 3:58 am
Our helpdesk need to be able to setup new logins on one of our SQL 2005 boxes and also to do password resets. To make it easy for them they need to do this through the SSMS GUI. I'm looking for the least permissions they require to do this and I'm struggling to find a way that requires less than sysadmin (which I'm sure can't be right!)
I have setup a user 'Bob' and made them a member of the 'SecurityAdmin' server role and also the 'db_securityadmin' database role in the relevant database. I also then ran:
'grant alter any user to Bob'
in the database. This user now has the following permissions:
serverCONNECT SQL
serverALTER ANY LOGIN
serverVIEW ANY DATABASE
databaseCREATE SCHEMA
databaseCREATE ROLE
databaseCONNECT
databaseALTER ANY USER
databaseALTER ANY ROLE
databaseALTER ANY APPLICATION ROLE
databaseVIEW DEFINITION
At this stage Bob can do pretty much all aspects of user management via the GUI. However the only way he can change passwords is by issuing ALTER LOGIN statements in T-SQL code. Trying to change passwords via the GUI gives a permission denied error.
According to this Microsoft support article (http://support.microsoft.com/kb/931976) only members of the sysadmin role can change passwords via the GUI.
I'd like to think there's a way round this that doesn't involve sysadmin rights. Even having the ALTER LOGIN statement in a saved script is not what I want. Remember these are non-SQL people and I'd like them to be using the GUI with as little rights as possible.
cheers
June 3, 2008 at 8:33 am
From reading that link, it sounds like you have it right - they would have to be a sysadmin in order to reset a password via the GUI (article mentions that it would prompt for old password, whereas you say you are getting "permission denied" error - whichever...)
Any thought been given to using Windows Authentication? Then the helpdesk wouldn't even need SSMS at all.
"Got no time for the jibba jabba!"
-B.A. Baracus
June 3, 2008 at 8:41 am
Unfortunately it's the usual story of 3rd party apps still only offering mixed mode authentication. In this case it's one of the market leaders in its field and we've only recently upgraded to the latest release, yet windows authentication still won't be available until sometime next year.
thanks anyway
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply