March 26, 2009 at 2:30 pm
We have an ERP system running SQL 2005 SP2. We run a database backup, compress, transfer and restore from Production to Warm servers.
We run sp_changelogin to update the server login with the restored database user ids. However we run into a problem. When a user is removed from production, the user login remains on the warm servers syslogins. Currently we make a mental note and go to the warm server and delete the user login from security.
Does anyone have a script to compare the two servers or a process that we could run that would provide output when there is a difference or better yet be able to delete the login on the warm server when it doesn't exist on the prod server.
March 26, 2009 at 2:39 pm
I'd use a DDL trigger, capture the drop user from production and build a script to run on the warm server.
March 26, 2009 at 3:06 pm
Hi Steve,
That would be good and all but I am on my way out at this position and altering the delivered database from the ERP company is not what I recommend. Any updates will completely wipe out any customization as it does a full truncate, drop indexes and triggers...
I wish this was easy (and oh cannot use replication or mirroring and such)
March 26, 2009 at 3:07 pm
The DDL trigger is on the instance, not the database. Won't touch the ERP db.
If you don't want to do this, I'd recommend not using SSMS to remove logins. Instead build a script that you put in a login on and it connects to both the hot and warm instances, and drops the login.
March 27, 2009 at 8:48 am
Ok maybe because I am racing around trying to complete all loose ends before I leave this job..
How can I create a DDL trigger that tells the warm server a login has been removed on the production server?
I guess I am not seeing past the trees in the forest....
March 27, 2009 at 8:57 am
I think it'd involve connecting to a remote server (either via OPENROW or Linked Server)
compare the sys.logins table, and DROP LOGIN
Look at the Microsoft SP sp_help_revlogin
http://support.microsoft.com/default.aspx?scid=kb;EN-US;918992
To go to extreme, you could delete ALL logins on WARM servers, and scheduled to run the results of sp_help_revlogin nightly to re-create all logins (with same SID and password)
March 27, 2009 at 9:16 am
Thanks for the info...I remember about two years ago revisiting this. See the users do not drop and add often. In fact probably once every six months.
Dropping then comparing than readding is just more complexity to an ERP system that cannot do simple mirror/replication/log shipping..
This 'feature' actually will cause more work...
thanks all again
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply