Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or logins, or modify the SA login's password. I would like to grant my domain account SYSAdmin privileges.

I have tried the following, in both situations I right clicked and ran SQL management studio as Administrator, and used Windows authentication to log in to the local SQL express.

I created a local admin account, with administrator privileges and logged in to my PC as that. I have logged to my PC as a Domain Admin.

I understand that if I log in as an administrator using windows authentication I should have full privileges, what can can be stopping me from getting this access?

share|improve this question

migrated from stackoverflow.com Sep 14 '12 at 0:07

This question came from our site for professional and enthusiast programmers.

    
Give this a try: mssqltips.com/sqlservertip/2682/… –  Shawn Melton Sep 14 '12 at 2:22

1 Answer 1

up vote 3 down vote accepted

SQL Server manages its own security. Just because you have local Windows administrator rights doesn't automatically grant you access.

That said, there are two approaches to get back in, using local administrative rights:

  1. Impersonate the NT AUTHORITY\SYSTEM account. There are two methods to do this, as I blogged about here. These methods allow you to leave the server running while recovering access. If there is no login for that system account, you have to use the second method (which is easier, and if you don't care about downtime, the one you should use anyway because it's safer).

  2. Restart the database engine service in single user mode, which does grant local administrators sysadmin rights over the instance. Do what you need to do, then restart the instance in multi-user mode. This is the backdoor which was created precisely for the situation you're in; however, you do have to interrupt service to do it.

share|improve this answer
    
+1. #2 is the option I'd opt for. –  Thomas Stringer Sep 14 '12 at 12:09
    
Option 1 worked for me, I did not try 2. –  PatFromCanada Sep 18 '12 at 15:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.