SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Keeping logins in sync for DR server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cwalston
Starting Member

25 Posts

Posted - 10/17/2007 :  16:36:29  Show Profile  Reply with Quote
Hi all,

I have a couple 2000 Standard servers that have DR counter-parts and we CONSTANTLY are out of sync with the logins/passwords.

Unforntunately due to the vendor apps, we are using SQL passwords...

I started going down the path of daily dropping the logins off the DR box and using sp_help_revlogins on Prod to create a sql script and then running that on DR...but have had a lot of little stupid problems along the way.

I wanted to just poke my head up out of cube land and see what other methods that the much more talented DBA's out there are doing it!

I really apprecitate any advice!!

Thanks!
Chris

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/17/2007 :  16:38:36  Show Profile  Reply with Quote
"sp_help_revlogins"

I haven't used them since SQL 6.5, but they used to work Just Fine. What problems are you having now?

SQL Server version pls?

Kristen
Go to Top of Page

RyanAustin
Yak Posting Veteran

Canada
50 Posts

Posted - 10/17/2007 :  17:12:09  Show Profile  Click to see RyanAustin's MSN Messenger address  Reply with Quote
I was able to find a stored proc a while ago ( can't remember where to reference it so I do apologize about that). All it takes is to run exec sp_fixusers against what ever database logins are not in sync.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_fixusers]

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END


CLOSE fixusers
DEALLOCATE fixusers
END
Go to Top of Page

cwalston
Starting Member

25 Posts

Posted - 10/17/2007 :  19:26:47  Show Profile  Reply with Quote
Thanks for the replies!

I am on 2000 Standard edition.

sp_fixusers just syncs up the logins to the db users. It doesn't help when you add/drop users or change their passwords.

The problems that I have been having are small but odd. I tried to use a job to run the sp_help_revlogins proc and used the steps Output file to save off the results. First I tried to save the output on the DR share, but the job would error saying not a valid directory. So I saved it local. But then, the output file had all these [SQLSTATE 01000] statements on almost every line. When I run the command in Query Analyzer I don't get those errors. ARG!!!

Basically it seemed way to much trouble than it should be...so before I banged my head on the wall too hard, I thought I would ask around and see how others handle their DR boxes.

Thanks!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36647 Posts

Posted - 10/17/2007 :  19:31:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
I haven't fully worked out the kinks yet, but here is what I have used in the past when we move production to our DR site:


SET NOCOUNT ON

USE Admin
GO

CREATE TABLE QTW_Logins
(
	loginId int IDENTITY(1, 1) NOT NULL,
	loginName nvarchar(128) NOT NULL,
	passwordHash varbinary(256) NULL,
	sid varbinary(85) NOT NULL
)

-- openquery is used so that loginproperty function runs on the remote server, otherwise we get back null
INSERT INTO QTW_Logins(loginName, passwordHash, sid)
SELECT *
FROM OPENQUERY([SDSQL01\QTW], '
SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid
FROM master.sys.server_principals
WHERE
	type = ''S'' AND 
	name NOT IN (''sa'', ''guest'') AND 
	create_date >= ''12/31/2005''
ORDER BY name')

DECLARE 
	@count int, @loginId int, @loginName nvarchar(128), 
	@passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
	@sid varbinary(85), @sql nvarchar(4000), @password varchar(514)

SELECT @loginId = 1, @count = COUNT(*)
FROM Admin.dbo.QTW_Logins

WHILE @loginId < = @count
BEGIN
	SELECT @loginName = loginName, @passwordHashNew = passwordHash, @sid = sid
	FROM QTW_Logins
	WHERE loginId = @loginId

	-- if the account doesn't exist, then we need to create it
	IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
	BEGIN
		EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

		SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = ' + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) + ' HASHED, CHECK_POLICY = OFF' 
		EXEC (@sql)

		PRINT 'login created'
	END
	-- if the account does exist, then we need to drop/create; can't alter as hashed isn't supported
	ELSE
	BEGIN
		SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

		IF @passwordHashOld <> @passwordHashNew
		BEGIN
			EXEC master.dbo.sp_hexadecimal @passwordHashOld, @password OUTPUT

			SET @sql = 'DROP LOGIN ' + @loginName
			EXEC (@sql)

			SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = ' + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) + ' HASHED, CHECK_POLICY = OFF' 
			EXEC (@sql)

			PRINT 'login "altered"'
		END
	END

	SET @loginId = @loginId + 1
END

DROP TABLE Admin.dbo.QTW_Logins



The script needs to run on the DR/remote server and then references the primary/prod server in OPENQUERY.

Use at your own risk since I haven't fully tested it nor have I committed to using it each time we switch sites.

NOTE: This script works on SQL Server 2005 only. I'm sure it can be easily modified for SQL Server 2000 though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Edited by - tkizer on 10/17/2007 19:33:08
Go to Top of Page

JeffOrwick
Starting Member

2 Posts

Posted - 02/01/2010 :  21:02:54  Show Profile  Send JeffOrwick an AOL message  Send JeffOrwick a Yahoo! Message  Reply with Quote
The posts so far don't address an issue I'm having.

I need to Sync Server Level permission as well (i.e View Server State; Sysadmin role, etc.).

Has anyone developed scripts that address this?
Thanks.

Jeff Orwick, DBA
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36647 Posts

Posted - 02/01/2010 :  23:30:09  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by JeffOrwick

The posts so far don't address an issue I'm having.

I need to Sync Server Level permission as well (i.e View Server State; Sysadmin role, etc.).

Has anyone developed scripts that address this?
Thanks.

Jeff Orwick, DBA



Please start a new thread on this so that your question isn't buried in a very old topic.

To answer your question though, we use a tool made by Red Gate to sync those things.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/02/2010 :  02:19:54  Show Profile  Reply with Quote
@JeffOrwick : please don't send me private emails asking me to solve your problem offline. I'm not available to work for you for free. This is a public forum, the idea is you post your question in public space, people give you answers and suggestions, Google's spiders index it, other people find the solution.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000