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?

score 9 · Accepted Answer · edited Jan 10 '12 at 16:43

9

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.

edited Jan 10 '12 at 16:43

Justin Dearing

1,017
10
33

answered Dec 06 '11 at 18:42

mrdenny

27,074
4
40
68

score 3 · Answer 2 · answered Dec 06 '11 at 18:30

3

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.

answered Dec 06 '11 at 18:30

Jason Cumberland

1,559
10
13

1

That didn't work. I ended up having to restart into single user mode – Earlz Dec 07 '11 at 03:45
It didn't work for me either, but in fairness, I think I forgot to log off/on before checking it. – johntrepreneur Aug 12 '14 at 00:06

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

2 Answers2