How to allow the local Administrator to have access to SQL Server?

Question

One of our clients has an installation of SQL Server Express 2008 R2 that we more or less manage for them. Today I need to create a new database(a fairly rare occurrence). So I go to login as the user we usually use and we don't have permission to create databases.. No biggie, just remote desktop and login as Administrator locally to create a new user for this....

Well, turns out the latest version of SQL Express stopped providing the BUILTIN\Administrator login... There is the sa account, but no one can remember what the password is.

How can I reset the password for sa or enable the BUILTIN\Administrator login?

Solution 1

If Jason's suggestion doesn't work, restart the SQL Instance in single user mode and everyone in the local admin group should now have sysadmin rights so you can get into the system and fix the permissions.

Solution 2

There should be a local group created called SQLServerMSSQLUser$$SQLEXPRESS on the machine. Add the user to that, restart SQL service and then log off/back on and you should have the needed access. Then you can add the administrators group to the instance explicitly.

Comments

[user971102]

That didn't work. I ended up having to restart into single user mode

[johntrepreneur]

It didn't work for me either, but in fairness, I think I forgot to log off/on before checking it.