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.