备份权限 [英] Backup Permissions

问题描述

我在服务器上创建了一个可以访问某些数据库的新用户。

I created a new user on the server that will access certain databases.

但是当我备份或还原数据库时，出现错误消息：

But when I go to backup or restore the database I get the error:

C：\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
无法访问
在服务器上指定的路径或文件。确认您具有
必要的安全特权，并且路径或文件
存在..................

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.....................

该错误显示系统中的其他任何路径。即使用户和服务帐户拥有完全控制权限的用户。

The error shows for any other path in my system. Even those where the user and the Service Account has full control permissions.

但是，如果我键入完整路径并单击确定，它会抱怨它无法显示，但是它会备份或还原数据库。只是不显示路径的树状视图。

BUT, if I type the full path and click ok, it complains that it can't show, BUT it does backup or restore the database. Just doesn't show the tree view for the path.

如果我使用 sa 帐户进行操作，

If I do the operation using the sa account, the dialog shows all paths without complaint.

PS：已经将用户添加到 db_backoperator 角色。

PS: Already added user to the db_backoperator role.

需要什么权限？

推荐答案

您要使用SQL身份验证登录名进行连接还是Windows登录？如果是SQL身份验证登录名，您如何为Windows文件夹授予该SQL登录名完全控制权限？ Windows不知道您在SQL Server中创建的任何SQL身份验证登录名。请向我们确切显示我在服务器上创建了一个用户的意思-什么用户？什么服务器？ SQL Server或Windows？

Are you connecting using a SQL authentication login or a Windows login? If a SQL auth login, how are you giving that SQL login "full control permissions" to a folder in Windows? Windows has no idea about any SQL authentication logins you've created in SQL Server. Please show us exactly what you mean by "I created a user on the server" - what user? what server? SQL Server or Windows?

作为一种解决方法，您还可以创建一个存储过程，该存储过程以 sa 或Windows登录，它是sysadmin组的一部分，使该较弱特权用户能够执行。但是，我能够通过添加一个完全没有其他权限的peon用户并将其添加到 db_backupoperator 角色来备份数据库：

As a workaround, you could also create a stored procedure that executes as sa or a Windows login that is part of the sysadmin group, and give this lesser-privileged user the ability to execute. However I was able to backup a database by adding a peon user with no other permissions at all and simply adding them to the db_backupoperator role:

CREATE LOGIN peon WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO
CREATE DATABASE splunge;
GO
USE splunge;
GO
CREATE USER peon FROM LOGIN peon;
GO
EXEC sp_addrolemember 'db_backupoperator', 'peon';
GO
EXECUTE AS USER = 'peon';
GO
BACKUP DATABASE splunge
  TO DISK = 'C:\tmp\splung.bak' -- change this path obviously
  WITH INIT, COMPRESSION;
GO
REVERT;
GO

因此，我将验证SQL Server服务帐户是否具有足够的特权来编写到相关路径。我知道您说的是这种情况，但正如我所展示的，这似乎不是 peon 用户的问题，而是底层引擎的写入能力文件系统。如果您尝试上述备份命令而没有向 db_backupoperator 角色添加 peon ，则会出现此错误（它不会让您可以到达实际备份命令附近的任何地方或验证磁盘上的任何权限）：

So, I would validate that the SQL Server service account has sufficient privileges to write to the path in question. I know you said that this was the case but as I've shown this doesn't seem to be a problem with the peon user but rather the underlying engine's ability to write to the file system. If you try the above backup command without adding peon to the db_backupoperator role, you get this error (it doesn't let you get anywhere near the actual backup command or verify any permissions on the disk):

Msg 262, Level 14, State 1, Line 1
BACKUP DATABASE permission denied in database 'splunge'.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

如果这是Windows登录名，请验证用户实际上，对相关文件夹具有写权限。尝试使用除 C：\Program Files\ ... 下的层次结构以外的其他文件夹，不要尝试直接写到根目录（例如 C：\file.bak ）。

If this is a Windows login, then please validate that the user does, in fact, have write permissions to the folder in question. Try a different folder other than the hierarchy under C:\Program Files\... and don't try to write directly to the root (e.g. C:\file.bak).

这篇关于备份权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！