Symptoms
SQL database restore may fail with the following error:
avsql Error <40258>: sqlconnectimpl_smo::execute Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
RESTORE DATABASE is terminating abnormally.
Cause
Account configured for the Backup Agent service (NT Authority/SYSTEM by default) does not have sufficient permissions in SQL Server.
Resolution
Open SQL Server Management studio and make sure that the Backup Agent service account (either local SYSTEM or a dedicated backup account) reported in the backup log is added to SQL SERVER
sysadmin or
dbcreator role.
To assign SQL Server roles to the Backup Agent service account:
- On the SQL client, start SQL Server Management Studio.
- In the left pane of the SQL Server Management Studio window, expand the tree to display first the required SQL Server and then the Security folder.
- Under the Security folder, click Logins to display the users in the right pane.
- In the Logins list, right-click the Account (either NT Authority\SYSTEM or the dedicated backup account), and then click Properties.
- In the login properties dialog box, select the Server Roles page.
- In the Server roles box, make sure that that sysadmin is checked.