Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Restrict Access to a Database for Administrator Expand / Collapse
Posted Tuesday, May 26, 2009 7:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 9, 2009 6:28 PM
Points: 1, Visits: 9

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.

Post #723707
Posted Wednesday, May 27, 2009 12:54 AM


Group: General Forum Members
Last Login: Monday, July 7, 2014 6:44 PM
Points: 1,989, Visits: 864
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.
Post #723815
Posted Thursday, May 28, 2009 1:37 PM



Group: General Forum Members
Last Login: Monday, June 16, 2014 7:20 PM
Points: 2,897, Visits: 5,978
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.


To help us help you read this

For better help with performance problems please read this
Post #725405
Posted Monday, June 1, 2009 3:13 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 21, 2012 3:13 PM
Points: 516, Visits: 1,563
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.


Post #727019
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse