SQL Server 2008 R2 Express permissions -- cannot create database or modify users

Question

Recently just upgraded to SQL Server 2008 R2 Express. When I attempt to create a database after logging in using Windows Authentication with my id myuser I receive this error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
CREATE DATABASE permission denied in database 'master'. RESTORE HEADERONLY is terminating abnormally Error 262

If I try to add the sysadmin role to myuser, this is the error I receive:

Add member failed for ServerRole 'sysadmin'. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
User does not have permission to perform this action Error 15247

If I try to add this role to my user with T-SQL, using this command,

EXEC sp_addsrvrolemember 'ziffenergy\myuser', 'sysadmin';
GO

Here is the error I receive:

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

Does anyone have any suggestions? It seems that I can't do anything with database on the local machine. Please note that I am the administrator on the Windows 7 workstation I am using, and if I try to create or modify databases and/or users on our network IT Test database server using SQL Server Management Studio, I can do that with no problem.

Galibi

Answer 1

You may be an administrator on the workstation, but that means nothing to SQL Server. Your login has to be a member of the sysadmin role in order to perform the actions in question. By default, the local administrators group is no longer added to the sysadmin role in SQL 2008 R2. You'll need to login with something else (sa for example) in order to grant yourself the permissions.

Holms

Thank you for the feedback. Yes, I have tried to login using sa; however, when I try to set the password for sa, I get this error message: "Change password failed for Login 'sa'. (Microsoft.SqlServer.Smo) An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) Cannot alter the login 'sa', because it does not exist or you do not have permission. (Microsoft SQL Server, Error: 15151)" Any ideas? – Galibi

It's the same issue. You won't be able to set passwords because your account didn't have the necessary permissions in your SQL instance. If you don't know the login details if an account with sysadmin privileges (or certain other more specialized privileges that are very unlikely in your situation) then you will need to get it from someone who does. – Holms

But I'm the person who installed the SQL Server 2008 R2 software on my workstation, so shouldn't I have the required permissions? – Galibi

If you specifically provisioned your account during SQL Server setup (it asks you for accounts to be added to the sysadmin group) then yes. It does not automatically add you to the sysadmin group, nor will it automatically add anything except sa. It should have asked you to set the sa password during setup. Do you remember doing that? – Holms

Excellent point! I logged out of my account, then logged in as administrator into the workstation, and from there executed the sp_addsrvrolemember stored procedure so now I have permission to administer the database as well from my roger.moore user account. Thanks! – Galibi

To further clarify, I actually didn't choose mixed mode authentication (in which case I would have specified an sa password). Instead, I chose "Windows authentication" but still didn't have permission to create a database until I performed the sp_addsrvrolemember action mentioned in my above comment. – Galibi

Answer 2

Coming late to the party, but I found this fantastic step-by-step guide on getting control of your SQLExpress instance if you don't have your sa password. I used this process to not only reset my sa password, but I also added my domain account to all the available server roles. I can now create databases, alter logins, do bulk operations, backups/restores, etc using my normal login.

To summarize, you use SQL Server Configuration Manager to put your instance into single-user mode. This elevates you to sysadmin when you connect, allowing you the ability to set everything up.

Edit: I've copied the steps below - kudos to the original author of the link above.

1. Log on to the computer as an Administrator (or Any user with administrator privileges)
2. Open "SQL Server Configuration Manager"
3. Click "SQL Server Services" on the left pane
4. Stop "SQL Server" and "SQL Server Agent" instance on the right pane if it is running
5. Run the SQL Express in single-user mode by right clicking on "SQL Server" instance -> Properties (on the right pane of SQL Server Configuration Manager).
6. Click Advanced Tab, and look for "Startup Parameters". Change the "Startup Parameters" so that the new value will be -m; (without the <>) example: from: -dc:\Program Files\Microsoft SQL.............(til end of string) to: -m;-dc:\Program Files\Microsoft SQL.............(til end of string)
7. Start the SQL Server
8. Open your MS SQL Server Management Studio and log on to the SQL server with "Windows Authentication" as the authentication mode. Since we have the SQL Server running on single user mode, and you are logged on to the computer with Administrator privileges, you will have a "sysadmin" access to the database.
9. Expand the "Security" node on MS SQL Server Management Studio on the left pane
10. Expand the "Logins" node
11. Double-click the 'sa' login
12. Change the password by entering a complex password if "Enforce password policy" is ticked, otherwise, just enter any password.
13. Make sure that "sa" Account is "enabled" by clicking on Status on the left pane. Set the radio box under "Login" to "Enabled"
14. Click "OK"
15. Back on the main window of MS SQL Server Management Studio, verify if SQL Server Authentication is used by right clicking on the top most node in the left pane (usually ".\SQLEXPRESS (SQL Server )") and choosing properties.
16. Click "Security" in the left pane and ensure that "SQL Server and Windows Authentication mode" is the one selected under "Server authentication"
17. Click "OK"
18. Disconnect from MS SQL Server Management Studio
19. Open "Sql Server Configuration Manager" again and stop the SQL Server instance.
20. Right-click on SQL Server instance and click on "Advanced" tab. Again look for "Startup Parameters" and remove the "-m;" that you added earlier.
21. Click "OK" and start the SQL Server Instance again
22. You should now be able to log on as "sa" using the new password that you have set in step 12.

Totalitarianism

Works also for Sql Server 2008 R2 Enterprise. Thanks! – Weichsel

Thanks alot. It is working for me in SQL Server 2008 R2. You saved my many days.... – Corker

Answer 3

In SSMS 2012, you'll have to use:

To enable single-user mode, in SQL instance properties, DO NOT go to "Advance" tag, there is already a "Startup Parameters" tag.

1. Add "-m;" into parameters;
2. Restart the service and logon this SQL instance by using windows authentication;
3. The rest steps are same as above. Change your windows user account permission in security or reset SA account password.
4. Last, remove "-m" parameter from "startup parameters";

Carliecarlile

Answer 4

I followed the steps in killthrush's answer and to my surprise it did not work. Logging in as sa I could see my Windows domain user and had made them a sysadmin, but when I tried logging in with Windows auth I couldn't see my login under logins, couldn't create databases, etc. Then it hit me. That login was probably tied to another domain account with the same name (with some sort of internal/hidden ID that wasn't right). I had left this organization a while back and then came back months later. Instead of re-activating my old account (which they might have deleted) they created a new account with the same domain\username and a new internal ID. Using sa I deleted my old login, re-added it with the same name and added sysadmin. I logged back in with Windows Auth and everything looks as it should. I can now see my logins (and others) and can do whatever I need to do as a sysadmin using my Windows auth login.

Relucent

Good point regarding domain accounts - probably a different SID under the hood. To verify this sort of thing, one can use powershell without too much pain: windowsitpro.com/windows-server/… – Totalitarianism

Answer 5

I have 2 accounts on my windows machine and I was experiencing this problem with one of them. I did not want to use the sa account, I wanted to use Windows login. It was not immediately obvious to me that I needed to simply sign into the other account that I used to install SQL Server, and add the permissions for the new account from there

(SSMS > Security > Logins > Add a login there)

Easy way to get the full domain name you need to add there open cmd echo each one.

echo %userdomain%\%username%

Add a login for that user and give it all the permissons for master db and other databases you want. When I say "all permissions" make sure NOT to check of any of the "deny" permissions since that will do the opposite.

Reeba

Answer 6

I got the same problem till I found this. I open a new query and run the following

RESTORE DATABASE <YourDatabase> 
FROM DISK='<the path to your backup file>\<YourDatabase>.bak'

then right-click on Databases -> Restore Database -> select Device -> click ... -> ADD your dataset -> Click Ok

Stirpiculture