Wednesday, September 28, 2011

How to Restore SYSADMIN Access to SQL Server When SYSADMIN Access is Lost

One of our DBAs installed SQL Server 2008 R2 as a clustered instance on a Windows 2008 R2 cluster with Integrated Security.  The only account with access to SQL Server was his personal account; he set his default database to a user database.   The user database became corrupt.  The result was he could not logon to SQL Server.  Neither could anyone else. 

The solution is rather easy and documented in BOL and MSDN here.  Simply set the SQL startup option to be -m.  This option starts SQL Server in single-user mode (only a single user can connect), the CHECKPOINT process is not started, and any local administrator connecting is assigned the sysadmin fixed server role. 

To add my logon id to the server as a logon with the sysadmin fixed server role, I added -m to the startup option and re-started the server.  I connected to the SQL Instance with SSMS.  I then added my logon id and added myself to the sysadmin fixed server role. 

I fixed the DBA's logon id by changing his default database (currently set to nothing as his default database was corrupt) to master.   I reminded the DBA to use Windows groups with role based security where the group's default database is master as that is our policy.

No comments:

Post a Comment