May 28, 2008 at 4:28 am
Hey all,
Does anyone have or know of a script that can compare the login accounts on one SQL 2005 server with another SQL 2005 server?
Basically I have a Live server with a SAN array, this is mirrored to a similar array on a DR SAN (that takes care of the data). But I want my DR SQL server to periodically run an SP to compare logins between Live and DR.
If it finds an account on Live which is not on DR, then it should import or create it on DR. If there is an account on DR which does not apperar on Live, then it should be deleted on DR.
Any takers?
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 6, 2008 at 5:37 am
Hey everyone,
OK nobody replied to the question so I have written the script myself.
Basically I used two cursors, one to create a list of logins which are on the live server but not dr (hence require creation on dr) and another to generate a list of logins which are on dr and not live (hence require deletion from dr)
Let me know what you think. Right now all it does is print the result to the screen but that’s an easy change! Hope it is helpful to someone.
-- Declare variables and create temporary tables
Declare @lname1 VARCHAR(50)
Declare @lname2 VARCHAR(50)
DECLARE @Cursor_RecNo1 CURSOR
DECLARE @Cursor_RecNo2 CURSOR
-- Cursor for Create logine statement on LIVE server
SET @Cursor_RecNo1 = CURSOR FOR
SELECT name FROM "LIVE-SERVER\LIVE_INSTANCE".MASTER.DBO.SYSLOGINS
WHERE name LIKE 'MYDOMAIN\%' AND NAME NOT IN (SELECT name FROM "DR-SERVER\DR_INSTANCE".MASTER.DBO.SYSLOGINS)
PRINT 'USE "DR-SERVER\DR_INSTANCE".MASTER'
OPEN @Cursor_RecNo1
FETCH NEXT FROM @Cursor_RecNo1 INTO @lname1
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'CREATE LOGIN [' + @lname1 + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master])'
FETCH NEXT FROM @Cursor_RecNo1 INTO @lname1
END
CLOSE @Cursor_RecNo1
DEALLOCATE @Cursor_RecNo1
-- Cursor for Create logine statement on DR server
SET @Cursor_RecNo2 = CURSOR FOR
SELECT name FROM "DR-SERVER\DR_INSTANCE".MASTER.DBO.SYSLOGINS
WHERE name LIKE 'MYDOMAIN\%' AND NAME NOT IN (SELECT name FROM "LIVE-SERVER\LIVE-INSTANCE".MASTER.DBO.SYSLOGINS)
PRINT 'USE "DR-SERVER\DR_INSTANCE".MASTER'
OPEN @Cursor_RecNo2
FETCH NEXT FROM @Cursor_RecNo2 INTO @lname2
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'DROP LOGIN [' + @lname2 + ']'
FETCH NEXT FROM @Cursor_RecNo2 INTO @lname2
END
CLOSE @Cursor_RecNo2
DEALLOCATE @Cursor_RecNo2
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 6, 2008 at 9:22 am
Adam,
I was writing the script but was caught up in getting the missing login creation statement execution on DR. I was able to figure out the missing logins but how to bring them from production was a challenge and is still a challenge for me.
Manu
June 10, 2008 at 4:10 am
Hi there Manu,
The script I wrote actually runs from the DR server and pulls data from the Live via a linked server. I went through several different logic versions but finally settled on one using two Cursors (though most people say to avoid), but they worked for me.
SET @Cursor_RecNo1 = CURSOR FOR
SELECT name FROM "LIVESERVER\LIVEINSTANCE".MASTER.DBO.SYSLOGINS
WHERE name LIKE 'MYDOMAIN\%' AND NAME NOT IN (SELECT name FROM "DRSERVER\DRINSTANCE".MASTER.DBO.SYSLOGINS)
OPEN @Cursor_RecNo1
This section sets up the Cursor which basically says, select all records from Live that are like 'MYDOMAIN\[anything else]' that are not in DR.
FETCH NEXT FROM @Cursor_RecNo1 INTO @lname1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstatement1 = 'CREATE LOGIN [' + @lname1 + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
The cursor works like an array or while loop, so each time it cycles the output is another missing login record. Basically what the above does is to go off and fetch the next record from the Cursor output and use it to set the @lname1 variable, which is then used in setting the value of @sqlstatement1 for the build statement for the new login.
EXEC (@sqlstatement1)
FETCH NEXT FROM @Cursor_RecNo1 INTO @lname1
END
Then the above executes the @sqlstatement1 (which is your login build statement) and because this script is running from DR, there is no need to specify server. Try replacing 'EXEC' with 'PRINT' so you can view the value of @sqlstatement1.
Remember there are two Cursors (@Cursor_RecNo1 and @Cursor_RecNo2), two login name vars (@lname1 and @lname2) and two sql statements (@sqlstatement1 and @sqlstatement2). Here '1' is for the creation of logins and '2' is for the deletion/drop of logins.
CLOSE @Cursor_RecNo1
DEALLOCATE @Cursor_RecNo1
I then close the Cursor and deallocate its memory space. To drop logins its essentially the same thing but in reverse.
I have modified mine to run initially to create an sp! That way I have created a job which I have scheduled to run weekly and output the results to a text file. This is the suggested usage and was always a part of the logic.
Hope this helps. If I am waffling or too confusing, please let me know and will try to re-phrase. I get that a lot 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 10, 2008 at 7:48 am
Hey All, Me again,
While talking to Manu I realised that the version of my script up here is ooooooooooooooooold!
Heres the new version which registers itself as an sp and does everything swisher! Hope its of use. 🙂
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_Compare_Live_to_DR_logins AS
BEGIN
SET NOCOUNT ON;
-- Declare variables and create temporary tables
Declare @lname1 VARCHAR(50)
Declare @lname2 VARCHAR(50)
DECLARE @Cursor_RecNo1 CURSOR
DECLARE @Cursor_RecNo2 CURSOR
DECLARE @sqlstatement1 NVARCHAR(MAX)
DECLARE @sqlstatement2 NVARCHAR(MAX)
-- Cursor for Create login statement on Live
SET @Cursor_RecNo1 = CURSOR FOR
SELECT name FROM "LIVESERVER\LIVEINSTANCE".MASTER.DBO.SYSLOGINS
WHERE name LIKE 'MYDOMAIN\%' AND NAME NOT IN (SELECT name FROM "DRSERVER\DRINSTANCE".MASTER.DBO.SYSLOGINS)
OPEN @Cursor_RecNo1
FETCH NEXT FROM @Cursor_RecNo1 INTO @lname1
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstatement1 = 'CREATE LOGIN [' + @lname1 + '] FROM WINDOWS WITH DEFAULT_DATABASE=[master]'
EXEC (@sqlstatement1)
FETCH NEXT FROM @Cursor_RecNo1 INTO @lname1
END
CLOSE @Cursor_RecNo1
DEALLOCATE @Cursor_RecNo1
-- Cursor for Drop login statement on DR
SET @Cursor_RecNo2 = CURSOR FOR
SELECT name FROM "DRSERVER\DRINSTANCE".MASTER.DBO.SYSLOGINS
WHERE name LIKE 'MYDOMAIN\%' AND NAME NOT IN (SELECT name FROM "LIVESERVER\LIVEINSTANCE".MASTER.DBO.SYSLOGINS)
OPEN @Cursor_RecNo2
FETCH NEXT FROM @Cursor_RecNo2 INTO @lname2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sqlstatement2 = 'DROP LOGIN [' + @lname2 + ']'
EXEC (@sqlstatement2)
FETCH NEXT FROM @Cursor_RecNo2 INTO @lname2
END
CLOSE @Cursor_RecNo2
DEALLOCATE @Cursor_RecNo2
END
GO
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 10, 2008 at 8:31 am
Thanks for the detailed and useful reply Adam. I have one question here:
How are you going to deal with sql logins(in case they are there on live server but not on DR box)?
I have one script that builds create sql login statements(with encrypted password) and can be xecuted on DR or other server to create same sql logins on destination server if they are not present on destination server.
Let me know your answer at your convenience.
Thanks,
Manu
June 10, 2008 at 9:08 am
In my case I am not worried about SQL login accounts as in my company only service accounts are created with SQL accounts all the rest are AD accounts (easier for auditing, licensing and user login). So there arent many and easy to either export or create.
I did consider building the functionality into this script but to be honest I cant really see the use. Microsoft have the SP_HELP_REVLOGIN script (http://support.microsoft.com/kb/918992/ SQL 2005) which is used to transfer the SQL logins (with their all important SID's). This script is relatively easy to doctor to pull out a specific group of users (for example if you wanted all logins that started 'SQL_'). If you want this I can post.
It is relatively simple to script a create of a new SQL login on another server with the login name and password (encrypted or otherwise) but unless the SID's match there is no point cause the databases will reference the SID's of users on the Live server.
My advice would be use SP_HELP_REVLOGIN to pull the accounts out. That way you get a script to create a duplicate account on your DR server (watch to ensure server roles are transferred). Then you can either reapply all (accepting that you will get errors on accounts that already exist or do a 'drop' operation first.
Hope this helps. Let me know if you want doctored MS script.
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 10, 2008 at 9:15 am
Thanks for the prompt reply Adam. I just wanted to confirm about how are you going to deal with sql logins, nothing else. I do agree that sp_help_revlogin script is best for this but was thinking if its possible to automate this. No issues will use revlogin script instead.
Manu
November 4, 2008 at 5:11 am
Does anyone have a script to pull out a list of sql logins with respective permissions under a sql instance?
November 4, 2008 at 12:38 pm
Hope this helps:
SET NOCOUNT ON
Drop Table #Usersdetail
Create Table #Usersdetail
(
IDintidentity(1,1),
DBName sysname collate database_default Null
,DBRole sysname collate database_default Null
,MemberName sysname collate database_default Null
,MemberSID sysname collate database_default Null
)
EXEC master..sp_MSForeachdb'
BEGIN
Declare @counter int
Select @counter=count(*) from #Usersdetail
INSERT INTO #Usersdetail(DBRole,MemberName,MemberSID)
EXEC ?..sp_helprolemember
BEGIN
Update #Usersdetail set DBName=''?'' where ID>=@counter
END
END'
--Select 'Use '+ DBName+char(10)+'Go'+char(10)+' sp_addrolemember '+''''+DBRole+''''+ ','+''''+MemberName+'''' from #Usersdetail
--Select LoginName, UserName, GroupName from #Usersdetail where UserName not like 'dbo' and LoginName is not NULL and groupname<>'public'
select usd.* from #Usersdetail usd
JOIN MASTER.DBO.SYSLOGINS SL
ON SL.SID=USD.MEMBERSID
AND SL.ISNTUSER=0
DROP TABLE #Usersdetail
MJ
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply