I restored a SQl database from one server to another and everything was exac
t
from one server to another besides the users. I had 2 users that would not
update correctly. I have tried to drop and recreate and had errors. Any
tips on getting users from one database to another?
TIAHi,
See the system procedure sp_change_users_login in the sql server books
online
Thanks
Hari
SQL Server MVP
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:444BE209-AA86-4D3A-B912-97529E0753EA@.microsoft.com...
>I restored a SQl database from one server to another and everything was
>exact
> from one server to another besides the users. I had 2 users that would
> not
> update correctly. I have tried to drop and recreate and had errors. Any
> tips on getting users from one database to another?
> TIA|||Hi
Look at sp_change_users_login with the Auto_Fix option.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:444BE209-AA86-4D3A-B912-97529E0753EA@.microsoft.com...
>I restored a SQl database from one server to another and everything was
>exact
> from one server to another besides the users. I had 2 users that would
> not
> update correctly. I have tried to drop and recreate and had errors. Any
> tips on getting users from one database to another?
> TIAsql
Showing posts with label users. Show all posts
Showing posts with label users. Show all posts
Friday, March 30, 2012
Monday, March 12, 2012
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
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
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
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
Friday, March 9, 2012
Restore of users when moving a database from one server to another...
Hi
When moving a SQL server database from one server to another you get
problem with the users.
Is there a easy way to recreate or fix the users that the database
allready contains or do I need to remove all security settings and
reapply them?
Thanks
/MagnusHi
You can create a Script about the database users and execute this when you restore database. Initially you should erase the users that has the new database and subsequently to execute the script in the Query analyzer
The Script you can create for Enterprise Manager, Rigth Click about the database, All Task, Generate SQL Script
*********************************************************
When moving a SQL server database from one server to another you ge
problem with the users
Is there a easy way to recreate or fix the users that the databas
allready contains or do I need to remove all security settings an
reapply them|||Look at sp_change_users_login in BOL.
Mike Abraham
"Magnus Wettemark" <magnus.wettemark@.staffware.dk> wrote in message
news:ec837e09.0402110332.3f9d9f17@.posting.google.com...
> Hi
> When moving a SQL server database from one server to another you get
> problem with the users.
> Is there a easy way to recreate or fix the users that the database
> allready contains or do I need to remove all security settings and
> reapply them?
> Thanks
> /Magnus
When moving a SQL server database from one server to another you get
problem with the users.
Is there a easy way to recreate or fix the users that the database
allready contains or do I need to remove all security settings and
reapply them?
Thanks
/MagnusHi
You can create a Script about the database users and execute this when you restore database. Initially you should erase the users that has the new database and subsequently to execute the script in the Query analyzer
The Script you can create for Enterprise Manager, Rigth Click about the database, All Task, Generate SQL Script
*********************************************************
When moving a SQL server database from one server to another you ge
problem with the users
Is there a easy way to recreate or fix the users that the databas
allready contains or do I need to remove all security settings an
reapply them|||Look at sp_change_users_login in BOL.
Mike Abraham
"Magnus Wettemark" <magnus.wettemark@.staffware.dk> wrote in message
news:ec837e09.0402110332.3f9d9f17@.posting.google.com...
> Hi
> When moving a SQL server database from one server to another you get
> problem with the users.
> Is there a easy way to recreate or fix the users that the database
> allready contains or do I need to remove all security settings and
> reapply them?
> Thanks
> /Magnus
Restore of users when moving a database from one server to another...
Hi
When moving a SQL server database from one server to another you get
problem with the users.
Is there a easy way to recreate or fix the users that the database
allready contains or do I need to remove all security settings and
reapply them?
Thanks
/MagnusHi.
You can create a Script about the database users and execute this when you r
estore database. Initially you should erase the users that has the new datab
ase and subsequently to execute the script in the Query analyzer.
The Script you can create for Enterprise Manager, Rigth Click about the data
base, All Task, Generate SQL Script.
****************************************
******************
When moving a SQL server database from one server to another you get
problem with the users.
Is there a easy way to recreate or fix the users that the database
allready contains or do I need to remove all security settings and
reapply them?|||Look at sp_change_users_login in BOL.
Mike Abraham
"Magnus Wettemark" <magnus.wettemark@.staffware.dk> wrote in message
news:ec837e09.0402110332.3f9d9f17@.posting.google.com...
> Hi
> When moving a SQL server database from one server to another you get
> problem with the users.
> Is there a easy way to recreate or fix the users that the database
> allready contains or do I need to remove all security settings and
> reapply them?
> Thanks
> /Magnus
When moving a SQL server database from one server to another you get
problem with the users.
Is there a easy way to recreate or fix the users that the database
allready contains or do I need to remove all security settings and
reapply them?
Thanks
/MagnusHi.
You can create a Script about the database users and execute this when you r
estore database. Initially you should erase the users that has the new datab
ase and subsequently to execute the script in the Query analyzer.
The Script you can create for Enterprise Manager, Rigth Click about the data
base, All Task, Generate SQL Script.
****************************************
******************
When moving a SQL server database from one server to another you get
problem with the users.
Is there a easy way to recreate or fix the users that the database
allready contains or do I need to remove all security settings and
reapply them?|||Look at sp_change_users_login in BOL.
Mike Abraham
"Magnus Wettemark" <magnus.wettemark@.staffware.dk> wrote in message
news:ec837e09.0402110332.3f9d9f17@.posting.google.com...
> Hi
> When moving a SQL server database from one server to another you get
> problem with the users.
> Is there a easy way to recreate or fix the users that the database
> allready contains or do I need to remove all security settings and
> reapply them?
> Thanks
> /Magnus
Subscribe to:
Posts (Atom)