Ken,

It was interesting to see how to reestablish users on a different instance of SQL Server by changing the SID.

I’ve wondered for a long time if this was as good, or maybe better, than the way we do it.

We perform the task every time we do a restore from production to test or development or build a new server. We run restores from production to test or development most every day.

We use a stored procedure that first saves the user’s security groups in a temporary table.

It then revokes the user’s access to the database.

Next it adds the user back to the database, and lastly back to the database Roles.

It works for both SQL Server and NT logins. We do have a standard that all security is granted through database Roles, so there is no code dealing with users who own database objects.

Since we are just using sp_revokedbaccess, sp_grantdbaccess, and sp_addrolemember and we rebuild all users, we don’t have to worry about SIDs.

Thanks for the interesting script.

JohnS