How to Connect to a SQL (2005, 2008 and R2) Server When You Are Completely Locked Out

Think of this scenario.

• You have forgotten (or do not have) the "sa" password
• The builtin\administrators account has been removed for security reasons
• You cannot connect to the SQL Server instance

Now what do you do? Let us see what you can do.
First off you need to shut down all the SQL Server related services. This can be done manually or you can use the batch file below. Either way you need to ensure that all SQL Server services are stopped. If you do the manual process see Image 1, which is a screen shot of the services you need to stop.
If you use this batch file, copy it to your editor like Notepad and save is as a batch file with the .bat extension. If you are stopping an instance you will have to modify the file.

@echo off
cls
echo.*****************************************
echo.**** Shutting Down SQL2005 Services ****
echo.*****************************************
echo.
echo. Shutting Down Integration Services...
net stop "SQL Server Integration Services"
echo.
echo. Shutting Down Full Text Search...
net stop "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Agent...
net stop "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Shutting Down Analysis Services..
net stop "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Shutting Down Reporting Services...
net stop "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Shutting Down SQL Browser...
net stop "SQL Server Browser"
echo.
echo. Shutting Down SQL Server...
net stop "SQL Server (MSSQLSERVER)"
echo.
echo.
echo. To stop the SQL Server Brower use this command: net stop "SQL Server Browser"
echo.
echo.
echo.**** Shut Down Completed ****
echo.
The service window should look like this once completed. See Image 2. Now you can open a command line window by clicking on START then RUN finally type: cmd, which will open a new window. From here you execute the .bat file you saved.

Once executed you will see that all the SQL Server services have stopped. If not, stop anything that might still be running. This step is important because if any other SQL Server services are still running you will not be able to connect when SQL Server is started in single user mode.

Now you need to start just the SQL Server in single user mode. In the command line window change to the directory that has SQL Server installed. In my configuration it's here on the C: drive:

cd\program files\microsoft sql server\mssql.1\mssql\binn

This location may be different if you are accessing an instance.

Type the following in the command window for the default instance

sqlservr.exe -m

For a named instance type

sqlservr.exe -m -s <instance name>

Once this is executing you will see screen scroll and then stop. This process is not hung just waiting as SQL Server is now running in single user mode.

Next open another command line window (cmd) and now you can use the sqlcmd tool included with SQL 2005 installation. In this new window type the following and hit enter.

sqlcmd -E

A new prompt will appear. The buildin\administrators account needs to be created and added to the proper role. The following is what you would type in the new command line window:

create login [builtin\administrators] from windows
go
exec sp_addsrvrolemember [builtin\administrators], [sysadmin]
go
shutdown
go

The first line will create a login called builtin\administrators as per the one created within the OS. GO command executes the previous command. Now you add this login to the sysadmin role and finally you shut down the SQL Server that is running in single user mode. Note the first command line window exits the execution of SQL Server and returns the prompt. You may now close this window.
Finally start up all your SQL Server services again and login. Hopefully you will find this article useful, actually I hope you'll never have to use it.

Thanks,
Rudy Panigas

PS. Below is a batch file you can use to start up all SQL Server related servers. I use both to stop and start SQL Server services as needed. If you have stopping an instance you will have to modify the file.

@echo off
cls
echo.---------------------------------------
echo.---- Starting Up SQL2005 Services ----
echo.---------------------------------------
echo.
echo. Starting Up SQL Server...
net start "SQL Server (MSSQLSERVER)"
echo.
echo. Starting Up SQL Agent...
net start "SQL Server Agent (MSSQLSERVER)"
echo.
echo. Starting Up Integration Services...
net start "SQL Server Integration Services"
echo.
echo. Starting Up Full Text Search...
net start "SQL Server FullText Search (MSSQLSERVER)"
echo.
echo. Starting Up Analysis Services..
net start "SQL Server Analysis Services (MSSQLSERVER)"
echo.
echo. Starting Up Reporting Services...
net start "SQL Server Reporting Services (MSSQLSERVER)"
echo.
echo. Starting Up SQL Browser...
echo.
echo. To start the SQL Server Brower use this command: net start "SQL Server Browser"
echo.
echo.---- SQL 2005 Servers Start-Up Completed ----
echo.

Collect SQL Server/Process Information - Quickly

If you need to get information out of your SQL server (2005 or 2008) quickly, execute the stored procedure and you will get a detailed report for the following areas

- Buffer statistics  - Memory statistics - General statistics - Locks statistics/Totals for Locks statistics
- TempDB statistics - Totals of Database statistics  - Plan Cache statistics - Transactions statistics
- SQL error statistics - SQL statistics (SQL compilations/recompilations) - Wait statistics - SQL  Execution statistics

This script displays data from the dynamic management  view called: sys.dm_os_performance and presents it in a report. I recommend that you look closer to this view for all your statistical needs.
If you find other information that you want to include, just modify this script to reflect the additional information.

Hope you find this script useful.

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DZ0RsMkdjeWEwR2M/edit?usp=sharing

DAC - What to Execute when Connected?

If your SQL server is in trouble and you cannot connect then the next option is to connect with the Dedicated Administrators Connection (DAC).

Once connected, you can collect some information on what is happening. Since you are under pressure, it would be nice to have a stored procedure to execute that displays information? Below is my script that does just that.

You want to execute the code below in the MASTER database to create the sp_dba_DAC stored procedure. We do this in MASTER because you generally have access to this database when the server is extremely busy.

Now open a CMD/DOS window and execute the following.

sqlcmd -A -S<server/instance name> -dmaster -E -Q"sp_dba_DAC" -oc:\dac-results.txt

Replace the <server/instance name> with your server or instance name.

Once you execute this command, a connection is made to the server/instance and the stored procedure "sp_dba_DAC" located in the MASTER database is executed. A file called "dac-results.txt" is created in the root of C: and the connection is terminated.

Open the "dac-results.txt" file with notepad (or any other editor) and you can quickly see the information.

Below is what is collected:

Shows SQL Servers information
Shows top 5 high cpu used statemants
Shows who so logged in
Shows long running cursors
Shows idle sessions that have open transactions
Shows free space in tempdb database
Shows total disk allocated to tempdb database
Show active jobs
Shows clients connected
Shows running batch
Shows currently blocked requests
Shows last backup dates
Shows jobs that are still executing
Shows failed MS SQL jobs report
Shows disabled jobs
Shows avail free DB space
Shows total DB size (.MDF+.LDF)
Show hard drive space available

Remember to create this stored procedure on your servers before you need to connect with DAC. Add additional stored procedure(s) or T-SQL code to provide even more information.

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DZEpONk9NLW1FOXM/edit?usp=sharing

Remove SQL Logins from All Databases and SQL Server

Have you ever had to delete a user from SQL server but are warned that the ID may still exist in a database? Or have you wanted to ensure that the account has been completed removed? Recently, a request came to me to remove many ids from several servers (SQL 2000 and SQL 2005) which via the EM/SSMS with would have taken some time. So I decided to make this job a little easier and more complete by writing a stored procedure that would help in this task.

The stored procedure will hunt for the user id specified in every database and then generate the T-SQL code used to actually delete the account. Below is an example.

Here I created a test id called "TestUser1"
To start, execute the code which will produce a stored procedure called: sp_dba_RemoveUser. Next supply the user account you want to delete and execute.
[sp_dba_RemoveUser] 'testuser1'

Finally the output is generated and you would copy and paste the results to the query window and execute.
Here is an example of the output generated.

-- *** Remove User/Login Tool ***
-- Version 1.2
--
-- This code will generate the t-sql code needed to remove a specific user from databases and
-- from SQL Server logins if needed.

-- ***********************************************************************************
-- *** Execute the following code to remove user from ALL DATABASES, if needed ***


USE master
GO
EXEC sp_dropuser [TestUser1]
GO

USE model
GO
EXEC sp_dropuser [TestUser1]
GO

USE ReportServer
GO
EXEC sp_dropuser [TestUser1]
GO

USE ReportServerTempDB
GO
EXEC sp_dropuser [TestUser1]
GO

-- ***********************************************************************************
-- *** Execute the following code to remove user from SQL Server login, if needed ***

IF EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'testuser1')
EXEC sp_droplogin testuser1

-- End of code

As you can see the T-SQL code goes to every database and drops the user's account. Once completed; it will drop the user from SQL server. Code is created if the user id exists in that database.

By using this method you can verify that the code generated is for the correct user and you can remove lines for database that you want to keep and/or you have to the option to delete the account for only the databases but keep the server login.

Hope this help you as it has helped me.

Thanks for reading

Upgrade SQL Server or not? The big questions for DBAs

Upgrading your SQL server has never been an easy question. Lately more and more DBAs have been asking why or why not to the upgrade question.

After attending the Dev Connection conference in Orlando FL, USA, the same question was asked over and over again. Here is a summary of what some experts are saying.

When thinking about upgrading SQL Server there are a few questions you need to ask yourself.

1. Is the current SQL server going out of support or required extra dollars to continue the support. For example within the next year or two, Microsoft will require extra money to support SQL 2005 as it is nearing end of life.
2. Does the newer version of SQL Server Denali (2011) or SQL 2008R2 have features that you need? If yes then you have your answer. If not, you still may want to upgrade to stay current.
3. Can you upgrade easily? If you are running SQL 7.0, 2000 or 2005 it will get harder to upgrade as newer versions become available. For example, if you are running SQL 7.0 and what to upgrade to SQL 2008R2, you can't directly. You need to upgrade to say version 2005 and then upgrade to 2008R2. If you are using SQL 7.0 or SQL 2000 you will need to revisit and most likely have to re write your DTS packages in SSIS (if you don't know what SSIS or DTS is then you should not upgrade!).
4. Does your company have time to do the testing once you have upgraded to ensure that your databases and applications are working correctly? There is a lot of testing required when you do an upgrade so make sure you and the development team have time to convert and test everything.

So upgrade or not? The answer is "It depends" Yes this is not the answer you wanted to read but it really does depend on all the points above. Once you start to look at each SQL server you want to upgrade you will see the questions above will help you decide.

Thanks for reading.