To Be….or not to Be….a sysadmin

If you are like me, or like most SQL DBAs for that matter, at some point you will either inherit or need to take over support for a SQL server that you are not a member of the sysadmin role on. Like most DBAs, I go through a very specific setup methodology to ensure that the appropriate security setups are done during installations, which always includes adding the local SQL administrators to the sysadmin role.

However, starting with SQL 2008 and beyond, members of the local Windows Administrators group are no longer automatically added to the sysadmin role in SQL. This is good, as it definitely is not a best practice for securing your data. But what if you didn’t do the setup? Or the server is not running in mixed-mode authentication? Or you don’t have the sa password? Or the previous administrator was hit by a truck yesterday and you need to get into it now to get the crashed server to get it running? Or a spaceship lands and…..well, you get the idea.

After combing through multiple pages of TechNet, MSDN, others’ blog posts, etc. it appears that there have been several different approaches to resolving this – from the sledgehammer approach or reinstalling SQL to attempting to start SQL in single-user mode and SSMS with a Run As Administrator. Different approaches have appeared to work for different DBAs. Whether you are dealing with SQLEXPRESS, MSSQL, etc, below are the two main methods that are the most effective.

STEP 1NOTE: this will add all local admin group users to the sysadmin role – per the MS archive http://archive.msdn.microsoft.com/addselftosqlsysadmin/

  1. Download the script listed in the above hyperlink, and copy the link to the SQL server that you will need to add yourself to.
  2. Log into the SQL server in question using your Windows authentication.
  3. Review all members of the local Administrators group of that server – remember – ALL users listed in this group get added tot he sysadmin role (it’s also a good idea to add yourself to this group if you are not there already)
  4. Execute the cmd script – you should see the following text:

NOTE: SQL will restart as it puts SQL into a maintenance\single-user mode and restarts it.

Adding ‘<domain>\<user>’ to the ‘sysadmin’ role on SQL Server instance ‘MSSQLSERVER.

Verify the ‘MSSQL$MSSQLSERVER service exists

Stop the ‘MSSQL$MSSQLSERVER service

The SQL Server (MSSQLSERVER) service is stopping.

The SQL Server (MSSQLSERVER) service was stopped successfully.

 

Start the ‘MSSQL$MSSQLSERVER service in maintenance mode

Add ‘<domain>\<user>’ to the ‘sysadmin’ role

Stop the ‘MSSQL$MSSQLSERVER service

The SQL Server (MSSQLSERVER) service is stopping

The SQL Server (MSSQLSERVER) service was stopped successfully.

 

The SQL Server (MSSQLSERVER) service is starting.

The SQL Server (MSSQLSERVER) service was started successfully.

 

‘<domain>\<user>’ was successfully added to the ‘sysadmin’ role.

Press any key to continue….

 

STEP 2NOTE: This method basically follows the same steps as the above script lists – however it is a little more elegant as you are manually doing just a single NT login as opposed to all users in the local Admin group on the server.

NOTE: the following steps were listed in another blog. I am re-posting them here, minus the screenshots, and providing a link to the original source – these are the same steps that I followed.

Also, this MSDN article was referenced by the author, which is the same article that I initially went by.

 http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

Okay, here is the step by step guide to add any account as System Administrator of SQL Server. This is documented and completely supported way to gain back the rights.  To log into SQL Server as SysAdmin, you need to have Local Administrator permission on the windows which is hosting SQL Server. If you don’t have that also then you may want to check with your windows team to get access (I am not a windows guy)

Steps to login to SQL Server as System Administrator. [Provided you are having windows local administrator permissions]

  • Stop the SQL Server Service using ANY of      below command.
    • Net Stop MSSQLServer (for default       instance) / Net Step MSSQL$<InstanceName> If you want to know       instance name, refer my earlier blog
    • Use SQL Server Configuration manager       and stop the SQL service. [Start>Programs>Microsoft SQL Server       2005>Configuration Tools>SQL Server Configuration Manager]
    • Use Services console [ Start >       Run > Services.msc] and locate the SQL instance you want to stop.
  • Start SQL Server in      Single User mode. You need to use start-up parameter m to start SQL Service in single      user mode. I prefer command line but its your choice.
    • Using command line
          

      • net start MSSQLServer /m        SQLCMD        [For default instance]
      •   

      • net start        MSSQL$<InstanceName> /m SQLCMD     [For named        instance]
    • Using configuration Manager
          

      • Locate the service which you have        stopped earlier. Go to its properties, “Advanced”, click on drop down at        “Startup Parameters”  and add ;-mSQLCMD as shown below

                       

You might notice that I have use SQLCMD after m. That’s not a typo. Many times, when you start SQL Server in single user mode, application grabs connection before you could. SQLCMD ensures that only SQLCMD program can connect to SQL Server when its running in single use mode. Here is the error you might see if above happens. SQLCMD should be in UPPERCASE. else that would also show same error. Please make sure there is no space between “;” and “-m”, the registry parameter parser is sensitive to such typos. You should see an entry in the SQL Server ERRORLOG file that says “SQL Server started in single-user mode.”

TITLE: Connect to Server
——————————
Cannot connect to (local)\SQL2k8R2.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘Contoso\demouser’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18461&LinkId=20476
——————————
BUTTONS:
OK
——————————

  • Connect to SQL Server      and add desired account in SysAdmin role.
        
    I normally prefer to      do it from SQLCMD (that’s why I added SQLCMD after m)
    • Open administrator command prompt.       (i.e. right click on command prompt shortcut and choose “Run As       Administrator”
    • Type sqlcmd –S <complete instance       name> For example
          

      • sqlcmd –S. (for default instance)
      •   

      • sqlcmd –S.\MyInstance
               For getting exact name, your my earlier blog
    • You are connected as System       Administrator, because you are part of local administrator group in       windows.
    • At this point you can add any       account to sysadmin because you are connect as sysadmin. Here is the       script I normally use to add local administrator group as a part of       SysAdmin group in SQL Server Instance. You may want to tweak this as       per your needs because I am adding all local admin as sysadmin which is       not a good practise.

USE [master]
GO
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N’BUILTIN\Administrators’, @rolename = N’sysadmin’
GO

  • Here is how it would look on sqlcmd      command prompt

 

  • Stop SQL Server      Service. You      can use any method as described in first step
  • Start SQL Service      normally. This      means that you need to remove startup parameter

Use whichever method is best suited to your needs. Either way will get you into the server and let you perform the needed steps that will get you added as a sysadmin into the server.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s