August 12, 2013 at 12:28 pm
Is there a way that I can set a SQL account to be able to add new users to only a certain database and no other databases?
August 12, 2013 at 3:56 pm
wjlugaila (8/12/2013)
Is there a way that I can set a SQL account to be able to add new users to only a certain database and no other databases?
If I understand you correctly, you want to create a login and that login should be able to create more login/user for certain databases, correct?
If so, you could create a login and assign securityadmin as the server role and map the databases you need access to?....shouldn't work?
Regards,
SQLisAwe5oMe.
August 13, 2013 at 4:40 am
Securityadmin is a server role which gives access to configure secutiry for server not to database level
refer http://technet.microsoft.com/en-us/library/ms188659(v=sql.100).aspx
assign db_securityadmin for the database.
Regards
Durai Nagarajan
August 13, 2013 at 5:04 am
There's a db_securityadmin database role - would that work for you?
John
August 13, 2013 at 5:56 am
The db_securityadmin role at the database level won't allow him to create the SQL login though will it?
August 13, 2013 at 5:57 am
No it won't, but he only asked to be able to add users to the database.
John
August 13, 2013 at 6:52 am
I need to be able to create new users and add those users to only one database.
Say you have a service account called "HeadCoach" and you want that account to create new users thru an application to a database called"Football", but "HeadCoach" can not have access to other databases, only "Football". Can this be done? If so, how?
August 13, 2013 at 6:55 am
Securityadmin or syadmin is needed to create a login but then they can give permission to any databases.
i dont think there is a option for this.
Regards
Durai Nagarajan
August 13, 2013 at 7:20 am
Thinking out loud (as it were,) what about something like this (kid of complicated:)
Create an account which is part of the SecurityAdmin Server Role, and DB_SecurityAdmin Role
Create a stored procedure in the DB, which uses "Execute As" the account above. The SP creates both the server and DB logins.
Grant ONLY your chosen account the ability to run the SP, and said account is otherwise no different than any other user.
Unfortunately, I'm at work and can't test this out here, but I think this might work. Nobody has the information to login as the account which is used in the SP, and the SP only has the commands needed to create the user accounts and set them up with the permissions they need.
If you need to customize the permissions for individual users, then I don't think this would work...
August 13, 2013 at 8:50 am
Would this SP be able to do other functions that SecurityAdmin can do with other databases?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply