Unable to restore a database - CREATE DATABASE permission denied in database 'master'

_{Friday, February 3, 2012 2:38 AM}

Hi all,

I am relatively new to Microsoft SQL Server 2008 R2. I asked for a database backup .bak file to learn myself. This file should be generated correctly since it works on some other machine. 

I am a local user of my XP machine (in a company network environment). When I select the .bak to restore the database, I receive the following error:

TITLE: Microsoft SQL Server Management Studio

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

ADDITIONAL INFORMATION:

CREATE DATABASE permission denied in database 'master'.

RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 262)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

I understand this could probably be a permission problem. I looked up online and a post suggested to change to single user model. I did so via SQL server Configuration Manager. Then I could add the .bak file to the window, but when I started to restore, the following error popped out:

TITLE: Microsoft SQL Server Management Studio

Restore failed for Server '3Q0CHN1\SQLEXPRESS'.  (Microsoft.SqlServer.SmoExtended)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: Cannot restore any database other than master when the server is in single user mode. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&LinkId=20476

I have been struggling in this trouble for two days. Would anyone provide some possible solutions? 

Really appreciate any help!

Sean

_{Friday, February 3, 2012 6:37 AM ✅Answered | 1 vote}

 do you by any chance know how to fix it other than re-installation (which would be my last try)?

1. First connect to SQL SERVER via SSMS as administrator.

2. Grant the sysadmin role to yourself

SP_ADDSRVROLEMEMBER 'yourDomain\YourName', 'sysadmin'

3. Disconnenct from SSMS and now connect with your login credentials and restore your DB.

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Friday, February 3, 2012 4:34 AM}

Hello Sean,

A few questions to start out:

1. When you installed the instance of SQL Server, did you add yourself as an administrator?
2. Do you know what security rights you have in the instance if you didn't install SQL Server in #1?
3. What is the result of the following query (note: you'll have to change the path)

RESTORE FILELISTONLY FROM DISK='Path_to_bak_file.bak'

-Sean

_{Friday, February 3, 2012 6:23 AM}

Hello Sean,

1. A few other online information also indicates this may be the issue. I don't think I added myself as an administrator. If this is the problem, do you by any chance know how to fix it other than re-installation (which would be my last try)?

2. I don't know if I understand the question correctly. I am the user of the system but not the administrator, which means some system configuration/security rights are withdrawn but I don't know exactly which.

3. I received the similar error:

Msg 262, Level 14, State 1, Line 1

CREATE DATABASE permission denied in database 'master'.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

Thank you very much,

Sean

_{Tuesday, February 7, 2012 11:01 PM}

If I execute this command:

EXEC SP_ADDSRVROLEMEMBER 'mydomain\roger.moore', 'sysadmin'

Here is the error I get:

Msg 15247, Level 16, State 1, Procedure sp_addsrvrolemember, Line 29
User does not have permission to perform this action.

Please note that I am the system administrator on the Windows 7 workstation I am presently using.  Why would I not have permission to add roles?  TIA.

_{Wednesday, February 8, 2012 4:38 AM | 1 vote}

 Roger,

 You are the systen administrator on the windows 7 workstation, but you are not the administrator in the SQL Server, thats why you ned to First connect to SQL SERVER via SSMS as administrator. and then you can execute the above comand without error.

Thanks
Manish

Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Wednesday, February 8, 2012 5:21 PM}

Manish,

Yes, I agree.  When I installed SQL Server 2008, I chose the "Windows authentication" for the database administrator (as opposed to Mixed Mode).  So that means that when I login, I should be the database administrator, right?

All the best,

Roger

_{Wednesday, February 8, 2012 6:33 PM | 1 vote}

Yes, I think so, I re-installed my SQL in the same way as you mentioned, and it works now. I hope yours is working too.

-Sean

_{Wednesday, February 8, 2012 10:08 PM}

Actually, it turns out that on Windows 7 I had to logout of my roger.moore account, then l logged in as administrator on the same computer and executed this command so then my roger.moore user would have permission to create and administer databases:

EXEC sp_addsrvrolemember 'ziffenergy\roger.moore', 'sysadmin';
GO

_{Thursday, February 9, 2012 2:14 PM}

Roger,

This is probably because when SQL Server was installed it was done under the admin windows account. During the insall setup will ask what windows users should be SA in the instance, by default it adds the current user running setup, but others can be added. By default SQL Server as of 2008 does not add the builtin\admins group as administrators of SQL Server.

-Sean

_{Wednesday, September 19, 2012 3:03 PM}

tnx Roger

Current logged user was not the one i've installed SLQ SERVER

I've solved adding Logins and Server Role  Sysadmin