SQL Server – Permission Denied When Creating a Database

restoresql server

Currently, my local instance looks like this

Database

I want to restore my user database

I have tried restoring my database my rightclicking on Databases and also tried right clicking and doing it on system databases but I get this error

Restore

I also have the same permission error when trying to create a new empty database.

Best Answer

If you are a Windows admin, you may create a sysadmin login for yourself following the procedure documented here: Connect to SQL Server when system administrators are locked out

Here it is in batch file form to run (from an elevated command prompt), for a default instance:

net stop mssqlserver 
net start mssqlserver /mSQLCMD 
sqlcmd -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssqlserver 
net start mssqlserver 
sqlcmd -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

or for a named instance:

net stop mssql$sqlexpress 
net start mssql$sqlexpress /mSQLCMD 
sqlcmd -S (local)\sqlexpress -Q "if not exists(select * from sys.server_principals where name='BUILTIN\administrators') CREATE LOGIN [BUILTIN\administrators] FROM WINDOWS;EXEC master..sp_addsrvrolemember @loginame = N'BUILTIN\administrators', @rolename = N'sysadmin'" 
net stop mssql$sqlexpress
net start mssql$sqlexpress
sqlcmd -S (local)\sqlexpress -Q "if exists( select * from fn_my_permissions(NULL, 'SERVER') where permission_name = 'CONTROL SERVER') print 'You are a sysadmin.'"

Note that if you create the login for BUILTIN\administrators and you've got User Account Control enabled then you'll have to elevate to connect. If you create a login for your individual account you won't need to elevate.

Related Question