Delete Older Backup History from msdb Database

-- Gets the oldest backup date in the backupset table
SELECT MIN(backup_finish_date)
FROM msdb.dbo.backupset

-- Gets the newest backup date in the backupset table
SELECT MAX(backup_finish_date)
FROM msdb.dbo.backupset

-- Purges the backupset table of all records BEFORE the one below in quotes
USE msdb;
GO
EXEC sp_delete_backuphistory '6/1/18';

List all Stored Procedures on a SQL Server that get Executed

-- =============================================
-- Author: K Griffith
-- Create date: 09 Feb 2015
-- Description: Returns a listing of all SPs on the server that get used
-- Modify date: 02 Mar 2018
--=============================================

SELECT st.dbid [DB_ID]
, DB_NAME(st.dbid) [Database]
, OBJECT_SCHEMA_NAME(st.objectid,dbid) [Schema]
, OBJECT_NAME(st.objectid,dbid) [StoredProcedure]
, MAX(cp.usecounts) [ExecutionCount]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL
AND cp.objtype = 'proc'
AND st.dbid = 7
GROUP BY st.dbid
, DB_NAME(st.dbid)
, OBJECT_SCHEMA_NAME(objectid,st.dbid)
, OBJECT_NAME(objectid,st.dbid)
--, cp.plan_handle
ORDER BY [Database]

Splitting a Full Name Apart When It Has Been Stuffed into a Single Field

-- In case the need arises to split a comma-delimited name - for example, where the last and first name have been stuffed into the same field such as 'Lastname, Firstname'

SELECT [UserName]
  , RTRIM(LTRIM(LEFT([FieldName], CHARINDEX(',', [FieldName]) -1))) [LastName]
         , RTRIM(LTRIM(STUFF([FieldName], 1, CHARINDEX(',', [FieldName]), ''))) [FirstName]
FROM [DatabaseName].[dbo].[TableName]

Querying the Default Trace File

  It has always been a bit of a pain having to open up a SQL Server trace file with SQL Server Profiler and try to find the information I am seeking. It's true that the trace can be saved to a table and queried, but this method is SO EASY. It is also great for auditing if you do not have SQL Server 2008 R2 Enterprise installed (We run SQL Server 2008 R2 Standard here at work). 

  There are several ways to run this query. Here is one that I am currently using in production to track any configuration changes:

DECLARE @TRC_PATH VARCHAR(500)

SELECT @TRC_PATH = CONVERT(VARCHAR(500), value) 
FROM fn_trace_getinfo(DEFAULT)
WHERE property = 2 

SELECT TEXTData, HostName, ApplicationName, DatabaseName, LoginName, SPID, StartTime
  , EventSequence
FROM fn_trace_gettable(@TRC_PATH,1) fn
WHERE TEXTData LIKE '%configure%'
AND SPID<>@@spid
ORDER BY StartTime DESC

  Of course you can change things around to suit your own needs - different fields in the SELECT, change the conditions of the WHERE clause, etc.

FYI, I got this info from this great, great article available at mssqltips.com: http://www.mssqltips.com/sqlservertip/2364/capturing-and-alerting-on-sql-server-configuration-changes/

Disable All Enabled Jobs and Re-enable Them

  I recently came across a situation where I was working with a vendor that was upgrading one of our applications. He asked me to backup the production database and disable all jobs on the production SQL Server. I could just go through the SSMS GUI and do all of this, but I like to automate anything I can. It keeps me on my toes and (most of the time) makes it to where I don't have to be remoted in or at my desk if something needs to happen during off hours. 

  Simply disabling all of the jobs should be pretty easy, right? Jobs in SQL Server are stored in the msdb database in the sysjobs table. Since I want to disable all the jobs, I need to change the [enabled] tinyint field in the sysjobs table and change it to 0 (0 for disable, 1 for enable). Easy peasy:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 0

However, there is one small problem with this. I this particular situation, there are 15 jobs on this SQL Server and only 13 of them are enable. There are 2 disabled jobs on this server, but we do use those jobs from time to time, so I don't want to delete them. The jobs will need to be re-enabled when the vendor finishes the work, but if I simply run:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 1

that enables ALL of the jobs, including the 2 that need to stay disabled. So here is how I got around it. I decided to create a table and store the job_id (the unique identifier for the job in the sysjobs table) for each enabled job in it. I can use our good friend, the SELECT INTO statement, to create this table on the fly:

USE [msdb]

SELECT job_id
INTO enabled_jobs
FROM sysjobs
WHERE [enabled] = 1

So now I have a snapshot of the enabled jobs at a given point in time. I can then run an UPDATE statement and disable all of these jobs for the vendor:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 0
WHERE job_id IN
  (SELECT job_id
  FROM enabled_jobs)

But there is just one more little problem: I want to automate this. Since I need to take my snapshot of which jobs are enabled at a point in time, I may need to create and recreate the enabled_jobs table over and over - especially if this code is to be reusable. So let's try this:

USE [msdb]

-- DROP TABLE if it already exists
IF OBJECT_ID (N'dbo.enabled_jobs') IS NOT NULL
  DROP TABLE enabled_jobs

-- Get the job_id of all jobs that are currently enabled and create a new table
-- on the fly to hold those IDs
-- Store these job_ids in a table so that there is a record of the jobs that were 
-- enabled and the ones that were not
SELECT job_id
INTO enabled_jobs
FROM sysjobs
WHERE [enabled] = 1

-- Disable all jobs that are enabled
UPDATE sysjobs
SET [enabled] = 0
WHERE job_id IN
  (SELECT job_id
  FROM enabled_jobs)

Now I can put all of this in a stored procedure and reuse it. Since we do not drop the enabled_jobs table at the end of our statement, it still exists and I can use it later to re-enable these jobs when our vendor has completed the work. It will re-enabled the ones that were previously enabled and keep the ones that were disabled disabled:

USE [msdb]

UPDATE sysjobs
SET [enabled] = 1
WHERE job_id IN
  (SELECT job_id
  FROM enabled_jobs)

I hope you enjoyed this entry and that maybe it even saved you a little time or that you learned something.

Changing the Restrict Access Option in SQL Server

Ever get something like this annoying popup when you try to change a SQL Server database from SINGLE_USER to MULTI_USER?

This is what always happens to me when a database is already locked down into SINGLE_USER mode and I try to change the property in the GUI. In order to change it, I run this T-SQL in SSMS (SQL Server Management Studio):

ALTER DATABASE [MyDatabase]
SET MULTI_USER WITH ROLLBACK IMMEDIATE;

If there is already an operation going on in the database, the "WITH ROLLBACK IMMEDIATE" will take things back to their previous state before the transaction started. After the statement completes, more than one authorized user should be able to access the database. Always use caution when performing any ALTER DATABASE statement on a production server. When a database is locked down in SINGLE_USER mode, an icon of a person will appear next to the database in SSMS:

If you refresh your view of the databases in object explorer after running the above T-SQL, the person icon should disappear from the database indication that it is in MULTI_USER mode.

The DATALENGTH() Function

Yesterday I was getting a very interesting error in SSRS. A user was trying to export data to Excel and it was basically telling her that it was too much data to fit into Excel. I began to suspect that one of the fields in the report was quite large. I went to run a standard T-SQL query to see exactly how large the field in question was. The field was named [Description] and it's type was text:

USE [MyDatabase]

SELECT LEN([Description])
FROM MyTable

Of course, you know what happened:

Msg 8116, Level 16, State 1, Line 3
Argument data type text is invalid for argument 1 of len function.

So, the LEN() function cannot be used on text type fields. Was there a LEN() equivalent for the text type? A little googling revealed that there was:

DATALENGTH()

This function was exactly what I needed to get the length for a text type field.

Rebuilding Indexes with T-SQL

In my last position, I ended up writing a lot of T-SQL. I have recently taken on a new position and I am the company's sole DBA, so there is less T-SQL, but it certainly has not gone away completely. Case in point, SQL Server's maintenance plans are pretty cool and very simple to implement, but they are not bullet proof. Last week I kept getting an alert an index rebuild job had failed. I took a look at the maintenance plan, troubleshot it, and even rebuilt it but it continued to fail. I then decided to look at the underlying T-SQL associated w/ this job. It was failing on one particular index rebuild and succeeding on the rest of them. However, this still shows up in the job as a failure. I decided to script out the index rebuild and since then I have had no issues with the job. I opted to leave the CATCH block empty. Depending on how important the job is, it may be wise to have something in the CATCH that writes to a table so that one can be aware of exactly which index rebuilds failed.Additionally, I opted to sort in the tempdb as opposed to the DB itself. Once again, that one is your call. It will depend on how your environment is set up as to whether or not this is a good idea.

USE [DB_NAME_GOES_HERE]

DECLARE @SQL VARCHAR(3000)
, @START INT
, @LAST INT
, @TABLE_NAME VARCHAR(200)
, @IX_NAME VARCHAR(200)

CREATE TABLE #TEMP(ID INT IDENTITY(1,1), [TABLE_NAME] VARCHAR(200)
, [IX_NAME] VARCHAR(200))

INSERT #TEMP
SELECT '[' + S.NAME + '].[' + T.NAME + ']' AS [TABLE_NAME], I.NAME AS [IX_NAME]
FROM SYS.INDEXES I
INNER JOIN sys.tables T
ON I.object_id = T.object_id
INNER JOIN sys.schemas S
ON T.schema_id = S.schema_id
WHERE I.INDEX_ID > 0
ORDER BY T.NAME, I.NAME

SET @START = (SELECT MIN(ID) FROM #TEMP)
SET @LAST = (SELECT MAX(ID) FROM #TEMP)

WHILE @START <= @LAST
BEGIN

SET @TABLE_NAME = (SELECT [TABLE_NAME] FROM #TEMP WHERE ID = @START)
SET @IX_NAME = (SELECT [IX_NAME] FROM #TEMP WHERE ID = @START)

SET @SQL = ' BEGIN TRY
ALTER INDEX [' + @IX_NAME + '] ON ' + @TABLE_NAME + '
REBUILD WITH ( PAD_INDEX = OFF
, STATISTICS_NORECOMPUTE = OFF
, ALLOW_ROW_LOCKS = ON
, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF
, SORT_IN_TEMPDB = ON )
END TRY

BEGIN CATCH ' +
-- If you want something to happen in the Catch block
-- , place that code here
' END CATCH'

EXEC (@SQL)

SET @START = @START + 1
END

DROP TABLE #TEMP

Great Quote of the Day

This concept is simple and primary, but often overlooked:
"...it is a good idea to create non-clustered indexes on all foreign keys. You will always run lots of queries, and also use the JOIN operator, based on your primary and foreign keys."
- Wagner Crivelini
From the article SQL and the JOIN Operator

Converting a Julian Date

I had to accomplish this at work yesterday. I did not see anything in the pre-defined SQL Server functions that would do it, so I started Googleing. Thank goodness for stackoverflow.com.


DECLARE @JULIAN_DATE INT = 2012146

SELECT DATEADD(D, CAST(SUBSTRING(
CAST(@JULIAN_DATE AS VARCHAR), 5, LEN(@JULIAN_DATE) - 4) AS INT) - 1,
        CAST('1/1/' + SUBSTRING(CAST(@JULIAN_DATE AS VARCHAR),1,4) AS DATE))
AS [NEW DATE]
     
http://stackoverflow.com/questions/2692361/most-concise-way-to-convert-julian-date-yyyyday-of-year-to-sql-datetime

This T-SQL is a little too verbose to be writing over and over again so later today I'm going to roll it up into a UDF (User Defined Function).

Modifying Data Through a View

The following requirements MUST be met to modify data through a view in SQL Server:

• The modification can reference exactly ONE table
• Columns in the view must reference columns in a table directly
• The column cannot be derived from an aggregate
• The column cannot be computed as the result of a UNION/UNION ALL, INTERSECT, EXCEPT, or CROSSJOIN
• The column being modified cannot be affected by the DISTINCT, GROUP BY, or HAVING clause
• The TOP operator cannot be used

NOT, AND, OR: the Boolean Operators in T-SQL

The boolean operators in T-SQL are NOT, AND, and OR and are executed in a SQL statement in that order.

• The NOT operator generally hurts query performance because indexes cannot be used for a WHERE clause when the NOT operator is used *
• Indexes can be used when the OR operator is specified, but all columns referenced by the OR must be included in an index or none of the indexes are used
• Performance is often improved when the AND operator is used because the AND operator generally results in a smaller result set

* Just like with the NOT operator, indexes cannot be utilized when a leading wildcard character is used. For example:

SELECT FirstName, LastName 
FROM [Users].[dbo].[Employees]
WHERE LastName LIKE '*ones' 

Get the Number of Rows from Each Table in a Database

  In order to see how many rows a particular table in a database has in SQL Server, you can simply right click on the table and select properties. It will be in available in one of the sections. However, what if you need to see how many rows every table in the database has at the same time? I find this query works well for me in those cases:

SELECT T.NAME, S.ROWS
FROM SYSINDEXES S
INNER JOIN SYS.TABLES T
 ON T.OBJECT_ID = S.ID
WHERE S.INDID > 2
ORDER BY S.ROWS DESC

SQL Server Times Out When Adding a New Column to a Table

Recently at work, I needed to add an auto-incremented int field to a table that had 2.5 million records and was about 50 columns wide. Not a terribly large table to some folks, but our server is not the most robust. So every time I tried to do this in the UI in SSMS, it would process for a while and then time out and tell me something to the effect that the changes could not be save to the table. I then got the bright idea just to try this as a query:

ALTER TABLE [database].[dbo].[tableName]
ADD ID INT IDENTITY(1,1) NOT NULL

It processed in something like 2 second and worked like a charm. Weird that it should fail in the UI, but work as a script. I will keep this in mind. Also, it NEVER hurts to be able to write the T-SQL by hand to accomplish a task.

REPLACE() VS RTRIM() and LTRIM()

   I recently had a VERY interesting experience while cleaning up some data. I had about 8GB of SQL Server table data - all nvarchar(max) stuff. The tables needed to be compared to find the differences so the data really did need to be cleaned up. For a while I used an UPDATE statement that contained the REPLACE() function in it. It worked well, but took over an hour to complete. I began to think that there HAD to be a better way to do this. All I was really concerned about was trimming white space off the front and end of the field. I thought I would try a combination of the RTRIM() and LTRIM() functions just to see what would happen like so:

UPDATE table
SET field1 = LTRIM(RTRIM(field1))


   This one simple change shaved 40 minutes off my job! Why? Because REPLACE() has to crawl across each field character by character to find each what needs to be replaced. The TRIM functions are only concerned about white space at the beginning and end of the field and so, in this case, they are much faster. There is nothing quite like using the right tool for the job. Many thanks to a very wise DBA for explaining what was going on.

Delete All Tables but Keep the Database

I needed to delete all the tables from a database this afternoon but keep the database itself. I managed to dig up this little snippet by Googling and it worked like a charm:


EXEC sp_MSforeachtable 'DROP TABLE ?'


DB Mirror Failover Script

I had to manually failover about 25 principals to their mirror partners. After about 20 mouse clicks I thought "There HAS to be a better way" and began to Google. Behold:

ALTER DATABASE [Database Name] SET PARTNER FAILOVER

Be mindful - this can ONLY be done from the principal, not the mirror. If the principal is unavailable, the mirror can be forced to start up, but there is a possibility of data loss:

ALTER DATABASE [Database Name] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

Credit where credit is due: http://www.sqlservercentral.com/Forums/Topic829941-1549-1.aspx

Stored Procedure to get File Info from the Master DB

I just realized that I had not published this before. Here is the sp I use to get all the info I need regarding files from available DBs on a server:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: K Griffith
-- Create date: 04 Feb 2011
-- Description: Returns the space consumed on the DB Server or generic info about each
-- DB on the server depending on the parameter passed. Only
-- returns DBs NOT participating in mirroring.
-- =============================================
CREATE PROCEDURE [dbo].[sp_FileSizes]

@TYPE NVARCHAR(10)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @ID INT
DECLARE @MAX INT
DECLARE @DB_ID INT
DECLARE @DB_NAME NVARCHAR(300)
DECLARE @SQL NVARCHAR(500)
DECLARE @SQL1 NVARCHAR(500)

SET @ID = 5
SET @MAX = (SELECT MAX(database_id) FROM sys.databases)

CREATE TABLE #temp(DBName nvarchar(300), Name nvarchar(300), Available decimal(18,2), Used decimal (18,2),
Type nvarchar(5), FileID int)

WHILE @ID <= @MAX 
 BEGIN 
  SET @DB_ID = (SELECT D.database_id 
                FROM sys.database_mirroring M 
                INNER JOIN sys.databases D 
                  ON M.database_id = D.database_id 
                WHERE M.mirroring_role_desc IS NULL 
                  AND D.database_id = @ID) 
  IF @DB_ID IS NOT NULL 
    BEGIN 
      SET @DB_NAME = (SELECT NAME 
                      FROM sys.databases
                      WHERE database_id = @ID)


      SET @SQL = 'USE [' + @DB_NAME + ']' 
      SET @SQL1 = ' INSERT INTO #temp (DBName, Name, Available, Used, Type,  
                     FileID) 
                  SELECT ''' + @DB_Name + ''' AS DBName, Name, 
                   size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'')  
                   AS int)/128.0 AS AVAILABLE, 
                   CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128.0  
                   AS USED, 
                     type = CASE Type 
                              WHEN 1 THEN ''Log'' 
                              ELSE ''Data''  
                             END, 
                   file_id 
                 FROM sys.database_files' 
              
      EXEC (@SQL + @SQL1) 
    END 
    SET @ID = @ID + 1 
   END 
     IF @TYPE = 'SPACE' 
       BEGIN 
         SELECT SUM(USED)/1024 AS [Total Space in GB] 
         FROM #temp 
       END 
     ELSE 
         SELECT * 
         FROM #temp 
         WHERE Type = @TYPE 
         ORDER BY Type ASC, Available DESC 


     DROP TABLE #temp 
  END

Retrieving a list of DBs that are NOT participating in mirroring

I have a stored procedure that I use on all my servers that queries the master table and retrieves all the data I need regarding each DB siting on the server. I can then decide which files need shrinking, which DB needs more space, etc. I was recently tasked with mirroring. My sp started to fail because a database on the mirrored server acting as a mirror is not available. I changed my query to only grab the IDs of DBs NOT participating in mirroring an viola! My sp worked again. Here's the query:

SELECT D.database_id
FROM sys.database_mirroring M
INNER JOIN sys.databases D
ON M.database_id = D.database_id
WHERE M.mirroring_role_desc IS NULL

I wish I was smart enough to have come up w/ this on my own, but such is not the case. Here's the "credit where credit is due": http://social.msdn.microsoft.com/Forums/en/sqldatabasemirroring/thread/ded375ce-1f09-4070-b588-3d3cb91ed41a

Guide to Mirroring Databases on SQL Server 2008 Enterprise

Guide to Mirroring Databases on SQL Server 2008 Enterprise

  One of the first things to make sure of is that the drive structure is the same on the principal server and the mirror server. For example, if data files are stored at S:\SqlData and log files are kept at L:\SqlLogs on the principal server, this exact same drive structure must be mimicked on the mirror server.
  Additionally, mirroring was attempted between a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise as the principal and a Windows 2003 Enterprise Server with SQL Server 2008 Enterprise R2 as the mirror. The initial mirroring could be established and the failover could occur from the principal (SQL Server 2008 Enterprise) to the mirror (SQL Server 2008 R2 Enterprise). However, when a failover back to the principal from the mirror was attempted, the mirroring broke and could not be reestablished. The take away here is to make sure that both servers are running the exact same version of SQL Server (SQL Server 2008 Enterprise, for example).
  Lastly, keep in mind that mirroring is done at the database level as opposed to something like clustering which is done at the server level. Therefore, each database must be set up for mirroring individually.

1. Select a database for mirroring on the principal server. Right click on the database. Select ‘Tasks’ then ‘Back Up…’ Select the location to where you would like to back up the database. The backup will need to end up on the mirroring server and eventually be restored there.
2. On the mirror server in SSMS, right click on Databases underneath the server name and select ‘Restore Backup’. In the Restore Database window that opens, after you have selected a database name and the backup to be restored, click on ‘Options’ in the top left hand corner of the window. Make sure that the MDF and LDF files are named the same as they are on the principal. Also, check the ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’ option.
3. Go back to the principal server, select the same database, pick ‘Tasks’ and then ‘Back Up…’ again. On the Backup type dropdown, select ‘Transaction Log’. Make sure that the transaction log backup is in a location that can be accessed from the mirror so that it can be restored to the mirror.
4. Right click on the database on the mirror. The database should show as ‘(Restoring…)’ Select ‘Tasks’ -> ‘Restore’ -> ‘Transaction Log…’ Select the transaction log backup that you created from the principal. Select ‘Options’ in the ‘Restore Transaction Log’ window and click the radio button that states ‘Leave the database non-operational… (RESTORE WITH NORECOVERY)’. Then click the ‘OK’ button’
5. Go back to the principal server and right click on the database that you have selected for mirroring. Click ‘Tasks’ -> ‘Mirror…’ When the Database Properties window opens, click the ‘Configure Security’ button and go through the wizard.
6. If you wish to include a witness server, select the ‘Yes’ radio button in the second screen. Continue to go through the wizard. The databases that are being mirrored in this instance are all on servers on a private network behind a firewall, so DO NOT check the box for ‘Encrypt data sent through this endpoint’. Additionally, data encryption can cause failures if things are not set up properly. Unless it is absolutely necessary, do not encrypt the data.
7. The wizard will build endpoints on the principal, mirror, and witness servers. The default port for mirroring is 5022. On a Windows 2003 Server, if the Windows Firewall is not in use, no additional configuration should be necessary. However, if the Windows Firewall is in use on the server, the port may have to be specifically opened. By default on a Windows 2008 Server, all ports are locked down so 5022 will have to be specifically opened on a 2008 Servers.
8. If all goes well, there should be success messages beside each of the servers in the mirroring scheme if the endpoints get configured correctly. When the wizard concludes, select ‘Start Mirroring’ to finalize the mirroring set up. If no error messages appear, look at both the principal and mirrored databases in SSMS. The principal database should display a message stating ‘(Principal, Synchronized)’ next to the database name. The mirrored database should display a message that states ‘(Mirror, Synchronized / Restoring…)’ next to the database.

Additional Information
  If the mirrored databases are going to be used in an ASP.NET application, this information will need to be placed in the web.config file when the connection string is defined. The parameter is called 'Failover Partner' and it follows the Data Source parameter:


<add name="SqlCS" connectionString="Data Source=SqlPrincipal; Failover Partner=SqlMirror; Initial Catalog=master; Persist Security Info=False; User ID=user; Password=password123" providerName="System.Data.SqlClient" />


  Once again, Database Mirroring occurs at the database level, NOT the server level. This is important because the logins are created at the server level. Additionally, SQL Server uses a GUID (like 0xB7898239D38FA34D84F30B7404F5C67B) called an SID to identify a login, NOT the login name. So if we create the login ‘user123’ on both the principal and mirror servers and assign the same permissions to the mirrored database, when the database fails the login ‘user123’ will not work because the SIDs don't match on the servers. The following article presents a method to resolve this issue:
http://support.microsoft.com/kb/918992
On the principal server, open a new query window in SSMS and run the following query:

----------------------------------------- BEGIN QUERY ----------------------------------------------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR


SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

----------------------------------------- END QUERY -------------------------------------------------

This script will create 2 stored procedures in the master database: sp_hexidecimal and sp_help_revlogin. After the script has executed, run the following statement in a new query window:

EXEC sp_help_revlogin

The output will give you the ability to create logins with identical SIDs on the mirrored database. Here is a sample output from the script:

---- Login: User123
CREATE LOGIN [User123] WITH PASSWORD = 0x0100F6FF96F04564026C472234A73D7FD9B3CE467E6B9E066BBC
HASHED, SID = 0xCC1C8D1C6BF52640844476D7FFADB862, DEFAULT_DATABASE = [master],
CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Run the above script on the mirror server and assign the same permissions to the user ID to the database that exist on the principal server and everything should work correctly.