Moving SQL 2000 Logins to SQL 2005

If you backup a database on one server and restore it to another, you can have the problem where the database has logins associated to it, but the instance does not. One problem, is that the database has a unique SID associated with the login name. If you create a new instance login with the same name, it will generate a different SID than the database one. I’ve read that if you are using 2005 and above, you simply create the new login for the instance and run the following to sync up the SID’s.
USE YourDatabaseName
GO
EXEC sp_change_users_login 'Update_One', 'UserName', 'UserName'
EXEC sp_change_users_login 'Auto_Fix', 'UserName'

However, in my case I was moving a database from SQL 2000 to SQL 2005 and these did not work.
The Update_One produced:
Msg 15063, Level 16, State 1, Procedure sp_change_users_login, Line 143
The login already has an account under a different user name.

What I had to do was:
1. Restore the database to the new server (SQL 2005)
2. Do NOT create a new login yet.
3. Run the following to find the unique SID associated to the database user account. Next we create a
new instance login with the same SID as the database account.

-- Look up the SID from the database
USE YourDatabaseName
GO

SELECT D.name AS [DB_LoginName], D.sid AS [DB_SID], S.name AS [Server_LoginName], S.sid AS [Server_SID]
FROM sys.database_principals AS D LEFT OUTER JOIN sys.server_principals AS S ON D.name = S.name

-- Next, take that SID and put it in here to create the login account
CREATE LOGIN UserName WITH PASSWORD = 'Password', SID = 0xB0A2667BAEDE1B4AB93EAA0F9525DD21

You can re-run the SELECT query to verify that they are indeed the same.

Date Formatting Functions

Different date formats are often neccesary on many occasions. Here are some Scalar-valued Functions you can use to help. Simply call your function and give it the full datetime field and it will return the formatted value depending on which funtion you call. For example; dbo.fn_dateYM(fullDateField) would return YYYY-MM.


-- Enter in full date. Return Year and Month: 2009-10
CREATE FUNCTION [dbo].[fn_dateYM]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(7)
AS
BEGIN
DECLARE @dateYM varchar(7)
SELECT @dateYM = CAST(YEAR(@dateMDYTime) AS CHAR(4)) + N'-' + RIGHT('00' + LTRIM(RTRIM(CAST(MONTH(@dateMDYTime) AS CHAR(2)))),2)
RETURN @dateYM
END

-- Enter in full date. Return Year: 2009
CREATE FUNCTION [dbo].[fn_dateY]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(4)
AS
BEGIN
DECLARE @dateY varchar(4)
SELECT @dateY = CAST(YEAR(@dateMDYTime) AS CHAR(4))
RETURN @dateY
END

-- Enter in full date. Return Month, Day, and Year: 10-14-2009
CREATE FUNCTION [dbo].[fn_dateMDY]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(25)
AS
BEGIN
DECLARE @dateMDY varchar(25)
SELECT @dateMDY = convert(varchar(25), cast(@dateMDYTime as smalldatetime), 101)
RETURN @dateMDY
END

-- Enter in full date. Return Month: 10
CREATE FUNCTION [dbo].[fn_dateM]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(2)
AS
BEGIN
DECLARE @dateM varchar(2)
SELECT @dateM = CAST(MONTH(@dateMDYTime) AS CHAR(2))
RETURN @dateM
END

-- Enter in full date. Return Day: 14
CREATE FUNCTION [dbo].[fn_dateD]
(
@dateMDYTime smalldatetime
)
RETURNS varchar(2)
AS
BEGIN
DECLARE @dateD varchar(2)
SELECT @dateD = CAST(DAY(@dateMDYTime) AS CHAR(2))
RETURN @dateD
END