Gaining SQL Server SysAdmin Access

Chad Miller, 2011-10-22

I’ve seen this come a few times at work and I’m sure most you have experienced something similar.

Someone or an application installs SQL Server, doesn’t grant access to the DBA group and asks for DBA support.

In SQL Server 2008 and higher the built-in local administrators group is no longer automatically part of the SQL Server sysadmin role. You should add necessary logins to the sysadmin role as part of your SQL Server installation. Not automatically granting local administrators access to SQL Server is generally a good thing, however when the SQL Server installation is done by say another application then we see issues were support groups do not have access to SQL Server even though they are local administrators on the box. In the last few months I’ve seen this scenario several times and so has Argenis Fernandez (blog|twitter) as he has a helpful blog post entitled  “Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again.”  The post describes a technique of using the Sysinternals tool PsExec to gain SQL sysadmin access to SQL Server on which you already have local administrator access. The  post also links to a documented way of starting SQL Server in single user mode in order to gain SQL Server sysadmin access (see “Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out).” And finally the post mentions, but does not demonstrate a method of using the Windows Task Scheduler. If you’re interested in the how’s and why’s this works and how different versions of SQL Servers are well, different in security settings defaults I encourage you to give the post and comments a read.

Armed with information on how to gain SQL Server administration I looked at the various options. The psexec utility is blacklisted in my environment, blocked from download and listed as an untrusted application. The approach of starting SQL Server in single user mode requires taking SQL Server down and since the application is a quasi-production system restarting SQL Server would have to be coordinated or done after hours. So, I chose to to use the Windows Scheduler trick. This should work on Windows 2003/XP and higher and I’ve tested on Windows 2008 and Windows 7. The typical UAC things apply—you’ll need to run as administrator. The script I came up with is listed below. I figured if I’m getting a server were some other group performed the installation or configuration there’s no guarantee PowerShell will be installed, so I’m going old school Windows batch file on this. It feels wrong to post a Windows batch file  instead of a Powershell script on my blog, but I think using a batch file is the best approach for the problem. To use, save the script as AddDBA.bat and see the example syntax. The script must be run locally.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
@echo off
@if "%1"=="?" goto Syntax
@if "%1"==""  goto Syntax
@if "%2"==""  goto Syntax
rem **********************************
rem Script AddDBA.bat
rem Creation Date: 10/21/2011
rem Last Modified: 10/21/2011
rem Author: Chad Miller
rem ***********************************
rem Description: Adds a Windows Account to SQL Sysadmin Role
rem Use when you have local Windows admin access but lost SQL Sysadmin access
rem ***********************************
 
@echo ************************
@echo *** ServerInstance: %1
@echo ************************
set TMPFILE=%TMP%\AddDBA-%RANDOM%-%TIME:~6,5%.tmp
schtasks  /Create /TN AddDBA /SC Once /ST 12:00 ^
/TR "sqlcmd -S %1 -Q \"CREATE LOGIN [%2] FROM WINDOWS; EXEC sp_addsrvrolemember [%2],[sysadmin];\" -o \"%TMPFILE%\" -e" ^
/RU "NT AUTHORITY\SYSTEM" /F
schtasks /Run /TN AddDBA
schtasks /Query /TN AddDBA /V /FO List
rem Wait 5 seconds
PING 127.0.0.1 -n 6  >NUL
rem Display output file
type %TMPFILE%
schtasks /Delete /TN AddDBA /F
goto :EXIT
 
:Syntax
@echo Syntax: AddDBA ServerInstance WindowsGroupOrLogin
@echo Example: AddDBA Z001\SQL1 Contoso\DBAGroup
goto :EXIT
 
:EXIT

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads