In SQL Server 2005 and 2008 the local Administrators account is not sysadmin by default. This makes it even more important that the one setting up the Database also remembers to add a SQL Server admins group to the sysamin role. If this step is forgotten, the user installing the database server is the only one that will ever be sysadmin.
In some extreme cases I’ve seen situations where no one except some dude on vacation is sysadmin and there’s a bunch of applications that needs to be installed/upgraded. In these cases I have also been assigned Local Administrator rights on the server, but since the local Administrators group isn’t sysadmin either I still cannot login to the SQL server.
What to do!?
Thanks to Raul Carcia’s blog post it’s not that a big deal. The instructions is written for SQL Server 2005, but works equally fine on SQL Server 2008 and the only requirement is that you are a local server administrator.
Here’s what to do:
- Open the SQL Server Configuration Manager.
- In SQL Server Services, open the properties for the SQL Server instance you need access to.
- In the Advanced tab, locate Startup Parameters.
- Add “;-m” to the end of that line.
- Press OK and restart the SQL Server into “Maintenance Mode” or “Single User Mode” if you like. (check that a restart is OK first
) - Open a command prompt (right-click, “Run as Administrator” in Windows 2008) and go to C:Program FilesMicrosoft SQL Server100ToolsBinn
(C:Program FilesMicrosoft SQL Server90ToolsBinn for SQL2005) - Execute
sqlcmd /A /E /S<SERVERINSTANCE>
(use . for local default instance and .INSTANCE for local named instance)
- In the CLI, execute:
EXEC sp_addsrvrolemember 'DOMAINyourusername', 'sysadmin'; GO
- Return to the SQL Server Configuration Manager and restore the Startup Parameters to it’s previous settings.
- Restart the SQL Server instance to allow users to access it again.
Now, you should be able to login to the SQL server with sysadmin rights using your current user. This would also be a good point in time to actually establish a SQL Server Admins group (local or domain) to add to the sysadmin role to avoid having others to the above steps when you, yourself, happens to be on vacation.
As Raul Carcia point out in his original post, this is really a disaster recovery procedure and there’s definitely nothing sneaky about it since it leaves quite alot of trails in the event logs.
All in all, a Great article by Raul and all credit should go his way.
