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 |
|
sunnyjassal
Starting Member
28 Posts |
 Posted - 2004-09-08 : 14:16:18
|
| Hello I am new to SQL Server Administration so please bare with me.Here is what I am trying to do.I want to create a user that has all rights to a specific database. So far I have a user I have assigned him to a database. In the database access tab I have him checked as the the database assigned to and master (not sure if the master should be selected here).In the Server Roles I have him checked as ONLY Database Creators. But when I log into this account and try to restore a database I cant. Basically I want this person to have rights to only one Database and he should be able to do whatever to that database. He should be able to restore the database from a backup file also. When I try to restore the database and select file from devices via enterprise manager I get the error "229: Error execute permission denied on XP available media".Any help will be greatly appreciated.Thank you,Sunny |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-08 : 14:24:31
|
| You need to add the user to the db_owner database role. Do not give the user access to master.Tara |
 |
|
|
sunnyjassal
Starting Member
28 Posts |
Posted - 2004-09-08 : 14:31:14
|
The user has the following rights to the databasepublicdb_ownerdb_accessadmindb_securityadmindb_ddladmindb_backupoperatordb_datareaderdb_datawriterI took the user off the master databasequote:
Originally posted by tduggan
You need to add the user to the db_owner database role. Do not give the user access to master.Tara
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-08 : 14:34:47
|
I was incorrect on the db_owner thing. Here is what SQL Server Books Online says for permissions of RESTORE DATABASE command:quote:
PermissionsIf the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database. RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.In addition, the user may specify passwords for a media set, a backup set, or both. When a password is defined on a media set, it is not enough that a user is a member of appropriate fixed server and database roles to perform a backup. The user also must supply the media password to perform these operations. Similarly, RESTORE is not allowed unless the correct media password and backup set password are specified in the restore command.Defining passwords for backup sets and media sets is an optional feature in the BACKUP statement. The passwords will prevent unauthorized restore operations and unauthorized appends of backup sets to media using SQL Server 2000 tools, but passwords do not prevent overwrite of media with the FORMAT option.Thus, although the use of passwords can help protect the contents of media from unauthorized access using SQL Server tools, passwords do not protect contents from being destroyed. Passwords do not fully prevent unauthorized access to the contents of the media because the data in the backup sets is not encrypted and could theoretically be examined by programs specifically created for this purpose. For situations where security is crucial, it is important to prevent access to the media by unauthorized individuals.It is an error to specify a password if none is defined.
Tara |
|
|
|
sunnyjassal
Starting Member
28 Posts |
Posted - 2004-09-08 : 14:47:13
|
| I am having 2 problems now, as soon as I took this user off of master and log in with that user, I cannot see any database even the one the user is in.Second if i give this user database creator role then I still cannot see any database, but can click on restore database. Once I click on it I am getting the following error:229: SELECT permission denied on object 'backupset', database, 'master', owner 'dbo'Third if I give this user System Administrator rights then this user can get into any database which should not happen. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-08 : 14:50:40
|
| Well in order to restore, the permissions will allow them to restore any database. Is that okay? If not, this isn't going to work.Tara |
|
|
|
sunnyjassal
Starting Member
28 Posts |
Posted - 2004-09-08 : 15:45:53
|
| No I still cannot restore with just database creator role |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-08 : 22:39:48
|
| how about db_owner? |
|
|
|
|
|
|
|
|
Permissions