SQL permission

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sean - 24 Oct 2007 00:51 GMT

I've got a SQL 2000 Server setup having multiple databases. One of my users
wants to be able to backup and restore his database
using SQL database and use Linked Server feature in sql 2000. In SQL 2000,
if I grant him access Server and System administration roles,
he would have access to more than what he needs. He also be able to backup
other databases and restore to other databases.

Is there a way I can grant him only access to his database so that he can
backup and restore his own database and not other databases?
If so, what permissions level do I need?

Also for Linked Server, can I just grant him access to this feature as well?

Thanks

Reply to this Message

Linchi Shea - 24 Oct 2007 04:40 GMT

Make the user a member of the db_backupoperator fixed database role in that
particular database.

use yourDB
EXEC sp_addrolemember 'db_backupoperator', 'yourUser'

You don't need to grant any permission for the user to use a linked server.
Whether or not he/she can anything with that linked server depends on whether
he/she has the permission on the target source.

Linchi

> I've got a SQL 2000 Server setup having multiple databases. One of my users
> wants to be able to backup and restore his database
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

Sean - 31 Oct 2007 18:09 GMT

Linchi,

I have granted the user db_backupoperator for that user. But when the user
launches SQL Enterprise Manager and uses backup on his
database, and then when wants to select a backup destination, he gets the
following error.

Error 229: EXECUTE permission denied on object 'xp_availablemedia',database
'master', owner 'dbo'.

After reading a MS KB article on this, MS recommends grant sysadmin role for
backup. By doing so, the normal user
will have more privileges than he needs.

Any suggestion?

> Make the user a member of the db_backupoperator fixed database role in
> that
[quoted text clipped - 29 lines]
>>
>> Thanks

Reply to this Message

Sean - 31 Oct 2007 19:10 GMT

Linchi

Please ignore my previous post. I finally found the solution to my problem.
The user needed to be added
to master database access as public and need to have permission to
xp_availablemedia.

> Linchi,
>
[quoted text clipped - 46 lines]
>>>
>>> Thanks

Reply to this Message