Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have inherited administration responsibility for a SQL 2008 R2 server at work. The previous database administrator has left the company, and can no longer be contacted. At this point I have:

  1. Full admin rights to the Windows Server Machine that the SQL Server is running on.
  2. Membership in the 'Important Server System Administrators' domain security group

Unfortunately, it looks like the original DB admin never granted the above group actual permissions to administer the database. (there were plenty of bad practices all-around). So I find myself in the following situation: I can run queries against the database, but cannot do anything requiring admin privieleges, including granting additional privieleges to myself. I also cannot execute the sp_chengedbowner stored procedure to grant myself owhership of the database.

Is there any way I can use the fact that I have local windows server admin rights to grant myself database ownership? My backup plan is to have the IT admin change the password of the user account of the recently-departed employee, so that I can log on as him, but to do this will require a journey through the nine circles of bureacracy, so I want to see if there is an easier way.

Thanks for reading and your suggestions!

share|improve this question

1 Answer 1

up vote 1 down vote accepted

Start the sql server in single user mode. When it's running in single user mode it should treat local administrators the same as "sa". In order to start it in single user add a -m flag to the executable, and keep in mind you need to log in to SQL server with the LOCAL administrator NOT a domain administrator. That should allow you to then go in and change the sa password.

share|improve this answer
    
Thanks Dave! The single user mode is exactly what I have been looking for, without knowing its proper name. This approach worked great, with one footnote: at least on SQL Server 2008 R2, the flag to invoke the single-user mode is -m, not -s. –  Inverseofverse Sep 19 '10 at 2:33
    
Great! I'm using 2005 and didn't look to see if it was different. I altered my post so that in the future people will have the right flag in the answer :) –  Dave Holland Sep 19 '10 at 2:55

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.