Recovering sysadmin on your local instance

Ran across the issue where my SQLServer was installed without my knowledge, and needed to recover sysadmin access to it.

Step 1 : Check if you can log into sa

Sometimes the sa account still has the default password that comes with the instance. Each version is different, check your version’s default password online. In my case, because the instance was installed vanilla (right out of the box), the sa account is disabled, and with my permissions, I can’t enable it.

If you’re really lucky, you can just log in with the sa account and fix all your stuff. But it’s most likely that you’re not…

Step 2 : Force yourself in with single-user SQLCMD

Stack Overflow has a great guide, but I had SQLExpress, so my steps were slightly different.

First, figure out exactly what your SQL login (most likely windows auth login) is. If you can log into the server, you can expand the security > Logins to find your exact login, complete with domain. If you can’t log in at all, you won’t have a login, and we can fix that too.

Start > SQL Server > Configuration Tools > SQL Configuration Manager

Right-click on the appropriate instance of ‘SQL Server’ and Stop the service. If you can’t stop, make sure you’ve exited out of SMSS.

Start > run > cmd.exe
sqlserver.exe -sSQLExpress -m"SQLCMD"

This starts a new instance of SQLCMD as single-user mode. My instance is SQLExpress.

Start > run > cmd.exe
SQLCMD -S localhost\SQLExpress

Your first cmd window is keeping the SQLCMD connection open, you’ll need a new window (this one) to do SQL. My instance is SQLExpress.

SQLCMD -S localhost\SQLExpress

CREATE LOGIN [testAdmin] WITH PASSWORD=N'test@1234', DEFAULT_DATABASE=[master]
go

EXEC sys.sp_addsrvrolemember @loginame = N'<<DOMAIN\USERNAME>>', @rolename = N'sysadmin'
go

If you can log into the instance, you already have the login and don’t need to perform the first two lines that creates your account. Notice the ‘go’s after each command. If you don’t have them, SQL will not do anything ; neither with the go’s or without, it will not tell you one way or another if the action was performed or is successful – only if there was an error.

After you’ve done this, CTRL-C in your SQL CMD window (current window), and CTRL-C in your other (first) window. Start up the instance again in SQL Configuration Manager and see how you did.

Iterating stored procedures in SQL

You can iterate the procedures and functions in your database with a query like this:

SELECT * 
FROM  INFORMATION_SCHEMA.ROUTINES

Or, if you only want to see the stored procedures and functions that you have created yourself:

SELECT specific_name, routine_type, created, last_altered 
FROM INFORMATION_SCHEMA.ROUTINES
where
substring(specific_name, 1,3) != 'sp_'
and substring(specific_name, 1,4) != 'sys_'
and substring(specific_name, 1,7) != 'aspnet_'

SQL Transaction

USE [MyDatabase]
GO
/****** Object:  StoredProcedure [dbo].[ExampleTransaction]    Script Date: 08/18/2009 10:02:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		me
-- Create date:   08/16/2009 10:02:31 PST
-- Description:	...
--		 		...
-- =============================================
ALTER PROCEDURE [dbo].[ExampleTransaction]
--(
-- -- ...
--)
AS
	/* SET NOCOUNT ON */

	DECLARE @pkeyID INT
	DECLARE @error INT
	SET @pkeyID = 0
	SET @error = 0

	BEGIN TRANSACTION 
	
		INSERT INTO MyTable (...) VALUES (...)

	SET @error=@@ERROR
	IF @error <> 0
	BEGIN
		ROLLBACK TRANSACTION
		SET @pkeyID = -1
	END
	ELSE
	BEGIN
		COMMIT TRANSACTION
		SET @pkeyID=@@IDENTITY

	END

	RETURN @pkeyID

SQL Cursors

There is usually a better way than to use cursors, but when it cannot be avoided, template:


DECLARE @myID INT
DECLARE @myID2 INT
DECLARE mycursor CURSOR
  FOR SELECT ID, ID2 FROM MyTable

OPEN mycursor
FETCH NEXT FROM mycursor INTO @myID, @myID2

WHILE @@FETCH_STATUS=0
BEGIN
  -- do stuff
  FETCH NEXT FROM mycursor INTO @myID, @myID2
END

CLOSE mycursor
DEALLOCATE mycursor