No SQL Server Express logins have permission to create db

Question

I cant create a new db because there are only 2 logins:

sa
BuiltIn\Users

I get permission denied when trying to create the db even though I am the admin. How can I add a user with permissions at this point or must I reinstall?

Answer 1

I assume SQL Authentication is disabled and builtin\users is not sysadmin (ie. you locked yourself out).

Stop the Express service, start the server in admin mode (sqlservr -m -s SQLEXPRESS from command line), connect to the admin instance and add builtin\administrators back as sysadmin. Then stop the administrative instance (Ctrl-C) and start back the the normal instance.

Since Vista/Win7/Win2k8 remove the administrator token from your context under normal UAC, it makes sense to also add as sysadmin yourself explicitly (domain\user) so you don't have to use RunAsAdministrator to connect.

See How to add a Windows user to the sysadmin fixed server role in SQL Server 2005 as a failure recovery mechanism.

Answer 2

If you log in using SQL Server authentication as 'sa', surely you don't get permission denied?

Answer 3

In the default install of SQLExpress, the SA user is by default disabled. You can connect to your SQLExpress instance with SQL Server Management Studio Express and enable the user and make sure it has a nice strong password, just add a new user and give it the rights it needs or use the Windows integrated security.