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

User · Answer

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.

User · Answer

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     Add  -m   into parameters  Restart the service and logon this SQL instance by using windows authentication  The rest steps are same as above  Change your windows user account permission in security or reset SA account password  Last  remove  -m  parameter from  startup parameters

User · Answer

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

User · Answer

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    Log on to the computer as an Administrator  or Any user with administrator privileges  Open  SQL Server Configuration Manager  Click  SQL Server Services  on the left pane Stop  SQL Server  and  SQL Server Agent  instance on the right pane if it is running 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   Click Advanced Tab  and look for  Startup Parameters   Change the  Startup Parameters  so that the new value will be -m   without the  lt    example  from  -dc  Program Files Microsoft SQL              til end of string  to  -m -dc  Program Files Microsoft SQL              til end of string  Start the SQL Server 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  Expand the  Security  node on MS SQL Server Management Studio on the left pane Expand the  Logins  node Double-click the  sa  login Change the password by entering a complex password if  Enforce password policy  is ticked  otherwise  just enter any password  Make sure that  sa  Account is  enabled  by clicking on Status on the left pane  Set the radio box under  Login  to  Enabled  Click  OK  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  Click  Security  in the left pane and ensure that  SQL Server and Windows Authentication mode  is the one selected under  Server authentication  Click  OK  Disconnect from MS SQL Server Management Studio Open  Sql Server Configuration Manager  again and stop the SQL Server instance  Right-click on SQL Server instance and click on  Advanced  tab  Again look for  Startup Parameters  and remove the  -m   that you added earlier  Click  OK  and start the SQL Server Instance again You should now be able to log on as  sa  using the new password that you have set in step 12

User · Answer

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

[sql-server] SQL Server 2008 R2 Express permissions -- cannot create database or modify users

Examples related to sql-server

Examples related to permissions

Examples related to sql-server-2008r2-express