CODEWITHSUNDEEP
sql-serversql-server-2008database-backups
Alan Araya
Alan Araya

Reputation: 721

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
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.

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

PS: Already added user to the db_backoperator role.

What permissions are required?

Upvotes: 26

Views: 88280

Answers (5)

salman mallick
salman mallick

Reputation: 1

In the sql server management. I went to Database properties then permissions and granted backup permission to the public, I assume you can give to a specific user as well. That solved my problem.

Upvotes: -2

JasonH
JasonH

Reputation: 599

I am running Windows 10 (x64), SQL Server Express 2014, trying to restore a backup to a x86 SQL Server 2005 database so I can use the data and help solve a bug in our application. I ran into the same scenario with permissions when backing up and restoring a .bak file.

Same thing here, I am an Admin on my PC, I assumed (my mistake) that SQL Server Service was running under my Windows account (because I'd log onto SQL Management with my Windows credentials).

So what I did was go into Services in Windows, found SQL Server, right clicked into Properties, stopped the service, then went to the LogOn tab and changed the "Log on as" to "Local System account" and checked the box that says "Allow service to interact with desktop". Started the service, and I was on my way.

Since my environment is closed - for development only - this was a quick fix for me to get going. I should warn you, this is not a best practice for end-users! Just for us devs.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280340

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?

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

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.

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).

Upvotes: 18

RBarryYoung
RBarryYoung

Reputation: 56735

db_backupoperator is a Database Role, not a Server Role or a Windows permission. It only grants the user necessary access to the database to make a backup. It does not grant any rights to the server's file structure, which are needed to actually make the backup file.

IIRC, to access the file structure to make a backup, the user must either already have windows/domain rights to access it, or have the Server Role sysadmin to pick-up the SQL Server's own Windows access rights.

Also, to actually Restore a database the user will need the Server Role dbcreator.

Upvotes: 20

usr
usr

Reputation: 171188

My guess is that this is a problem with Windows authentication and integrated security. SQL Server sometimes impersonates the user logging on. Try adding Windows ACL permissions for the Windows user you are logging on with.

Upvotes: 1

Related Questions