May 26, 2009 at 7:16 pm
Hi,
I am a newbie and i need some help. I want to create a new database on the sql server but do not want the administrator to have access to it. We are a group of 3 people working and have the administartor password.The aim is to create a new database but only 2 out of the three need to have access to this new database. is this possible and how can i do it. We are working on SQL server 2000. this is urgent and help will be highly appreciated.
Thanks
Sudhin
May 27, 2009 at 12:54 am
You can't restrict the rights of a member of the sysadmin role: membership of that role results in a login bypassing virtually all security checks. If the login isn't a member of the sysadmin role then you've got free scope to use the other roles to limit its rights at the server level.
Similarly the dbowner role gives virtually unlimited rights within the scope of the individual database, but if the login is not a member of the dbowner role then you can tie down quite specifically what rights the login has within a given database.
One quick word of advice. I've had to tie down a login's permissions a couple of times. I found the GUI to be very easy to set things up once if you knew exactly what you wanted from the start, but it's a terrible way to experiment with different settings (it's also easy to forget a step if you're doing more than one: at least you've only got one login to worry about). If you expect this to take some experimentation I'd recommend scripting everything that you do: you'll find it much easier to make changes and review different iterations.
May 28, 2009 at 1:37 pm
I'm assuming that when you state
We are a group of 3 people working and have the administartor password.
you mean that you all share the same login (Sql log in I'm guessing).
The best way for you to help this situation would be for you to to each have your own log in (this is a pretty widely adopted industry best practice). This login can be mapped to windows users/groups as needed. Now that you are all not accessing the database server with the same credentials we can talk about setting up the security for the various logins. Glenn has some good advice on how to do that. I'd only add to make certain to follow the principal of least privilege as you assign the various rights to the log ins. Only give someone what they really need to do their job.
-Luke.
June 1, 2009 at 3:13 pm
If you mean Administrator a windows login that is part of the Administrators group and in Sql Server the BUILTIN\Administrators group still has the default permissions of being a Sql Server SysAdmin, then the only way to remove privileges is to remove the SysAdmin fixed server role from the BUILTIN\Administrators group. This can have some unexpectied consequences so search this site to get the full details of how to do this.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply