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:
- Full admin rights to the Windows Server Machine that the SQL Server is running on.
- 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!
Best Answer
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.