February 7, 2017 at 6:24 am
Hi.
We do a SAN snap on our staging SQL servers. That means that we create an exact copy of our production environment to our staging environment via the SAN (SAN uses VSS).
My problem is that after we do the san snap - I have users that no longer have access to thier DB's.
Not sure if they are truley orphaned because some of these are A/D accounts - they lose the DB 'user mapping'.
I have been using this:
ALTER AUTHORIZATION
ON DATABASE::[EDI]
TO [liqour\jimbeam];
GO
This works sometimes and sometimes gives an error - because we don't always refresh the SAN (job still runs).
There has got to be a better way for me to handle this. Looking for a global way if possible.
February 7, 2017 at 7:07 am
the fix for sql users is easy; use the procedure from microsoft to script out the users:
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server
in my case, i drop my sql logins and just recreate them form the script out of production. then the users in each db have the same sids, and the issue goes away.
for AD users reporting bad sids, that's wierd.
is dev in a clone of your AD, but all firewalled away, or is it really on your AD network? so mydomain\lowell is not really the same mydomain\lowell in Dev?
EXEC sp_help_revlogin
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply