August 17, 2008 at 7:42 pm
Please advise how to provide limited rights to a Sql Server user account so they can backup and restore only their database and not get into the rest of the system. Also, during restore can I limit them to one directory instead of letting them review the whole system?
Thanks
August 17, 2008 at 8:18 pm
You can give them the database role for backup (db_backupadmin) for their database only.
For file system, you would need to limit the rights that their accounts have. As far as backups, not sure you can limit the files they see.
Please post in the appropriate forum (moved to backups)
August 17, 2008 at 9:54 pm
You'll need to work with the NTFS permissions to restrict what files/folders their account has access to. I'd recommend creating an administrative account for them to use, rather than their current personal account. Make sure it is well documented, lest the permissions get overwritten!
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
August 18, 2008 at 10:21 am
You can give them the database role for backup (db_backupadmin) for their database only.
That would be the (db_backupoperator) role.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
August 18, 2008 at 3:04 pm
The db_backupoperator only allows rights to backup the database, not restore it.
To restore to a new database, you must have CREATE DATABASE permissions.
To overwrite an existing database with the restore, you have to be a member of the DBCREATOR server role (which also allows drop/alter of any db), or be the database owner (actually the owner, not just with db_owner perms).
See Permissions section of this page: http://msdn.microsoft.com/en-us/library/ms186858.aspx.
I've got a similar problem and am thinking of writing a stored procedure for the restore to avoid giving them perms I don't want them to have. I haven't gotten it done yet, or I'd post it.
As far as directory access - yup, you'd need to set the user perms on the server to restrict that.
August 19, 2008 at 7:51 am
You could install another instance of SQL Server for their app and give them admin/sa rights to that database instance.
That keeps them out of your main database(s) while giving them the access they need for theirs. You of course could also have sa rights and still schedule backups, etc as needed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply