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

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirlTechnically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>|||Thank You all.. I have in fact successfully backuped and restored my
database with the roles I assigned to the sql login and I did not need
sa privilege.
Thank is why I am stumped that the 3rd party tool would require the sa
privilege to do its job.
sqlgirl
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirlDo the new logins have permission to access the files on
the server ?
J
>--Original Message--
>Hello All,
>I have run into a situation that a 3rd party backup and
restore solution is
>requiring sa account to backup and restore databases in
my SQL 2K/SP3a
>server.
>I have created a login that is a member of
db_backupoperator and db_onwer
>database roles and database_creators server role. Isn't
that enough? does
>it absolutely need sa privilege? Please enlighten me if
you have run into
>the same scenario.
>Thanks,
>sqlgirl
>
>.
>|||Technically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
--
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>

restore permissions to a login

Hello All,
I have run into a situation that a 3rd party backup and restore solution is
requiring sa account to backup and restore databases in my SQL 2K/SP3a
server.
I have created a login that is a member of db_backupoperator and db_onwer
database roles and database_creators server role. Isn't that enough? does
it absolutely need sa privilege? Please enlighten me if you have run into
the same scenario.
Thanks,
sqlgirl
Technically the permissions you have assigned should be enough to backup and
restore the database. Whether the 3rd party solution has the need for the sa
account hard coded in the application, I do not know, but in that case I
would not use that solution. If they can't even get something as simple as
the permissions right (and the least the can do is just allow any member of
sysadmin as the login. I mean, what about SQL Server installations that use
Windows Authentication only?), what can you expect from the rest of the
application?
Jacco Schalkwijk
SQL Server MVP
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
|||Hi,
The users with server fixed role "DBCREATOR " can restore the daabase.
User with database fixed role "DBOWNER" can only backup the database , but
cannot restore. The user with role "db_backupoperator "
also can backup not restore.
Thanks
Hari
MCDBA
"Biva" <biva.yauchler@.redprairie.com> wrote in message
news:40a8e53b$0$974$39cecf19@.news.twtelecom.net...
> Hello All,
> I have run into a situation that a 3rd party backup and restore solution
is
> requiring sa account to backup and restore databases in my SQL 2K/SP3a
> server.
> I have created a login that is a member of db_backupoperator and db_onwer
> database roles and database_creators server role. Isn't that enough?
does
> it absolutely need sa privilege? Please enlighten me if you have run into
> the same scenario.
> Thanks,
> sqlgirl
>
>
|||Thank You all.. I have in fact successfully backuped and restored my
database with the roles I assigned to the sql login and I did not need
sa privilege.
Thank is why I am stumped that the 3rd party tool would require the sa
privilege to do its job.
sqlgirl
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!