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

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.

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to permissions

On npm install: Unhandled rejection Error: EACCES: permission denied Warnings Your Apk Is Using Permissions That Require A Privacy Policy: (android.permission.READ_PHONE_STATE) ActivityCompat.requestPermissions not showing dialog box PostgreSQL: role is not permitted to log in Android 6.0 multiple permissions Storage permission error in Marshmallow Android M Permissions: onRequestPermissionsResult() not being called pip install failing with: OSError: [Errno 13] Permission denied on directory SSH Key: “Permissions 0644 for 'id_rsa.pub' are too open.” on mac changing the owner of folder in linux

Examples related to sql-server-2008r2-express

How to get current instance name from T-SQL SQL Server 2008 R2 Express permissions -- cannot create database or modify users Unable to connect to SQL Server instance remotely Unable to connect to SQL Express "Error: 26-Error Locating Server/Instance Specified)