5/30/2023 0 Comments Backup master database![]() and don't try to write directly to the root (e.g. Try a different folder other than the hierarchy under C:\Program Files\. If this is a Windows login, then please validate that the user does, in fact, have write permissions to the folder in question. 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īACKUP DATABASE permission denied in database 'splunge'.īACKUP DATABASE is terminating abnormally. 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. ![]() So, I would validate that the SQL Server service account has sufficient privileges to write to the path in question. ![]() You can estimate the size of a full database backup by using the spspaceused system stored procedure. Therefore, the backup is usually smaller than the database itself. The backup contains only the actual data in the database and not any unused space. TO DISK = 'C:\tmp\splung.bak' - change this path obviously The backup operation copies the data in the database to the backup file. ![]() ![]() 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 ĮXEC sp_addrolemember 'db_backupoperator', 'peon' Please show us exactly what you mean by "I created a user on the server" - what user? what server? SQL Server or Windows?Īs 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. 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. ![]()
0 Comments
Leave a Reply. |