Restore prod to dev with different users

Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx

Restore prod to dev with different users

Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx
If you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.c om...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx

Restore prod to dev with different users

Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
ThxIf you have some additional users in your dev environments, then when you
restore your production db to dev, those users will not have access the
restored database.
The way I'd go about it, is to script all the users and their permissions,
save it a file and run it after the restore. Your file will typically
contain call to sp_grantdbaccess and GRANT, DENY and REVOKE commands.
Or simply have a script to create a database role, grant access to all
users, add the users to this group, and apply permissions on this group
Do it once, save the script, and reuse it when needed.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Shawn" <coloradocamper@.hotmail.com> wrote in message
news:9ff983ee.0408180619.26ec1ac8@.posting.google.com...
Does anyone have a solution to the restore problem from a production
environment to a dev/stage environment where different users exist?
For our enterprise ETL and reporting tools we use business process
(BP) IDs that differ between environments, and of course, developers
exist in dev/stage where they don't in production. Mapsid works if the
same logins exist. I have resorted to manually reviewing users and
their permissions in the database before restore, and then putting
everything back afterwards.
Thx