September 22, 2009 at 12:13 pm
I don;t think this is possible but I;d like to solicit input in case I'm wrong and or in case anyone has suggestions on how to handle/do this.
We are running SQL Server 2005 Pro and on it there is a DB 9rather small, just a few GB) that half a dozen users currently connect to using Access in some way as the front end. I just watch the DB side and setup the DB Users so i can't say exactly how they are using Access with thei DB but I'd guess it is a front end to the SQL DB as the back end.
The head user wants the ability to add & drop users at their leisure. Is there any way to grant sp_AddUser permission that does not give them permissions that go above the DB itself? Each new DB Users i connected to a single SQL Login so sp_AddUser would need to come after sp_AddLogin and that is then server level.
Suggestions on how to do this?
Kindest Regards,
Just say No to Facebook!September 22, 2009 at 12:28 pm
I would give the head user the db_securityadmin fixed DB role.
Then db_securityadmin can manage fixed database roles membership (db_datareader, db_datawriter, etc., but not db_owner) on database level only. He can add and drop database users using the existing server logins only. He cannot create/modify server logins and server level permisions.
September 22, 2009 at 12:36 pm
Maybe you could create ask the Net Admins to create a group with all the existing users in it, give the permission to the group. When your head user wants to add a user to access the DB, he just needs to add the user to the group. And when he wants to remove a user access to the DB, he just needs to remove the user from the group. Wont that work?
-Roy
September 22, 2009 at 12:43 pm
That is the best way, but requires all the users to be using integrated security.
The probability of survival is inversely proportional to the angle of arrival.
September 22, 2009 at 1:01 pm
sturner (9/22/2009)
That is the best way, but requires all the users to be using integrated security.
Good point since I left out the fact that they aren't all using Integrated Authentication.
At least the replies confirm my suspicion that there is no simple way to do this. I knew the ability to Drop Users at the DB Role level was in place and I just never had a need arise where someone at the DB Level wanted to create new users and so when they asked I said "Sure" because I did remeber their being the ability to drop users.
Oh well
Kindest Regards,
Just say No to Facebook!Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply