Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restrict Access to a Database for Administrator


Restrict Access to a Database for Administrator

Author
Message
sudhin.nangar
sudhin.nangar
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
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
Glenn Dorling
Glenn Dorling
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2995 Visits: 919
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.
Luke L
Luke L
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3398 Visits: 6123
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.

To help us help you read this

For better help with performance problems please read this
steve block
steve block
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 1563
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search