Automate syncing login ids of master db at remote server

Hi All,
I was hoping for some advice about automating syncing of login ids at
remote servers. I have implemented simple log shipping and that seems
to be working fine, any ids created on the database are replicated to
the standby database without issue, however any changes to the master
database logins are not replicated.
I have looked at the DTS "transfer logins task" however there appear to
be some limitations to this.
I have also looked at several scripts (sp_help_revlogin) provided by
MS, http://www.support.microsoft.com/?id=246133, however this process
seems to be manual, and I would like this to be automated, so that
should there be any changes to logins within the master db, they are
replicated to the standby server the next time the task is run.
Similarly I would like to sync any orphaned users at the standy server.
If anyone could please provide further information I would be grateful.
Many Thanks
Andrew
The way I do this in an automated manner is completely unsupported by
Microsoft.
I create a linked server for the standby. Then alter sp_addlogin as well as
sp_changepassword and add in a call to sp_addlogin and sp_changepassword to
the local server passing the same input arguments along. This causes the
logins to be added to both instances as well as handles password changes.
It is unsupported, because I modify system objects.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142333250.388701.45050@.j33g2000cwa.googlegro ups.com...
> Hi All,
> I was hoping for some advice about automating syncing of login ids at
> remote servers. I have implemented simple log shipping and that seems
> to be working fine, any ids created on the database are replicated to
> the standby database without issue, however any changes to the master
> database logins are not replicated.
> I have looked at the DTS "transfer logins task" however there appear to
> be some limitations to this.
> I have also looked at several scripts (sp_help_revlogin) provided by
> MS, http://www.support.microsoft.com/?id=246133, however this process
> seems to be manual, and I would like this to be automated, so that
> should there be any changes to logins within the master db, they are
> replicated to the standby server the next time the task is run.
> Similarly I would like to sync any orphaned users at the standy server.
> If anyone could please provide further information I would be grateful.
> Many Thanks
> Andrew
>
|||Mike,
Thanks for your help,
It is a novel approach, and ensures up to date login information.
As I understand whenever a user is created via either an application or
Enterprise manager, the sp_addlogin sp is called.
However what if the remote server is unavailable at the time that a new
user is being created on the primary db?
I am not sure if I have the skills to amend these scripts, particularly
given that they are system objects.
Thanks once again,
Andrew
|||I have continued to search the web for a solution and have found some
scripts provided by Umachandar Jayachandran at
http://www.sqlmag.com/Article/Articl...er_25710.html,
which seem to work, in that the logins ids are located within
sysxlogins table within the master db on the standby server, however
these logins are not visible under users within Enterprise Manager,
does this matter?
CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
set ANSI_NULLS OFF
set ANSI_WARNINGS OFF
DECLARE @.logins cursor
DECLARE @.name sysname, @.password sysname,
@.dbname sysname, @.language sysname,
@.sid binary(16), @.isntuser bit
SET @.logins = cursor fast_forward FOR
SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
l.isntuser
FROM [server\instance].master.dbo.syslogins AS l
WHERE l.loginname IS NOT NULL
OPEN @.logins
WHILE(1=1)
BEGIN
FETCH @.logins INTO @.name, @.password, @.dbname,
@.language, @.sid, @.isntuser
IF @.@.fetch_status < 0 break
IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
CONTINUE
IF @.isntuser = 0
EXEC sp_addlogin @.name, @.password, @.dbname,
@.language, @.sid, 'skip_encryption'
ELSE
BEGIN
EXEC sp_grantlogin @.name
EXEC sp_defaultdb @.name, @.dbname
EXEC sp_defaultlanguage @.name, @.language
END
END
DEALLOCATE @.logins
|||They should be visible. Enterprise Manager is looking at the sysxlogins
table.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"astrally2005" <andrewdritchie@.yahoo.com.au> wrote in message
news:1142602814.147621.269160@.e56g2000cwe.googlegr oups.com...
>I have continued to search the web for a solution and have found some
> scripts provided by Umachandar Jayachandran at
> http://www.sqlmag.com/Article/Articl...er_25710.html,
> which seem to work, in that the logins ids are located within
> sysxlogins table within the master db on the standby server, however
> these logins are not visible under users within Enterprise Manager,
> does this matter?
> CREATE PROCEDURE sp_Syncronize_Logins_from_prod AS
> set ANSI_NULLS OFF
> set ANSI_WARNINGS OFF
> DECLARE @.logins cursor
> DECLARE @.name sysname, @.password sysname,
> @.dbname sysname, @.language sysname,
> @.sid binary(16), @.isntuser bit
> SET @.logins = cursor fast_forward FOR
> SELECT l.loginname, l.password, l.dbname, l.language, l.sid,
> l.isntuser
> FROM [server\instance].master.dbo.syslogins AS l
> WHERE l.loginname IS NOT NULL
> OPEN @.logins
> WHILE(1=1)
> BEGIN
> FETCH @.logins INTO @.name, @.password, @.dbname,
> @.language, @.sid, @.isntuser
> IF @.@.fetch_status < 0 break
> IF is_srvrolemember( 'sysadmin', @.name ) IS NOT NULL
> CONTINUE
> IF @.isntuser = 0
> EXEC sp_addlogin @.name, @.password, @.dbname,
> @.language, @.sid, 'skip_encryption'
> ELSE
> BEGIN
> EXEC sp_grantlogin @.name
> EXEC sp_defaultdb @.name, @.dbname
> EXEC sp_defaultlanguage @.name, @.language
> END
> END
> DEALLOCATE @.logins
>