Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-05-16 : 10:20:59
|
| American Cocker writes "Hi,I am new to SQL Server and have encountered a situation that I am very surprised to see. It may be that I have evaluated the situation incorrectly, or, maybe there are accepted workarounds to this situation...We are committed to locking-down our servers as much as possible and giving access privileges only where required. When I created several, non-related, development databases on our new server, it was with the intent that the users would have full db_owner(ship) privileges but no server privileges. However, it seems that in order to Restore a database, the user requires db_creator privilege, which is a server-level Role privilege. I interpret this to mean that any user with this server-level privilege will have the ability to restore any database in that Instance. So... I started creating a different Instance for each of the non-related databases, but, it will not be long before this methodology gets out-of-hand...What is a good way to handle this situation?Thanks in advance." |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-05-16 : 10:40:49
|
| If a login has a database creator role assigned it's not necessary that this user can alter all databases. You grant acces to specific databases on the "database access" label in SQL server login properties.You set this option in "security-logins-right click-properties".So there is no need for different instances of SQL server. |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-05-16 : 11:39:43
|
Not sure which version of SQL Server you are running, but BOL from version 7.0 has this to say: quote:
If the database being restored does not exist, the user must have CREATE DATABASE permissions. If the database does exist, RESTORE permissions default to members of the sysadmin fixed server role and the db_owner fixed database role.
So as long as the database exists, they should be able to perform a restore as db_owner. This should give you the level of control you are looking for. Someone please correct me if this has changed in SQL2K.Jeff BanschbachConsultant, MCDBA |
|
|
|
|
|
|
|
|
Database Restore Role and Privileges