Restore SQL 2005 DB from remote location
- I've recently been involved in setting up a SQL Database on a central SQL 2005 Server to allow access from another server for an external vendor. The idea being that the vendor can remote into the Application server install/support the application on that server while hosting the SQL Database within our existing SQL infrastructure.The Scenario.1. Blank SQL 2005 Database created with a SQL account being setup as the owner of the DB.2. Vendor remotes into Application Server and using SQL Management Studio connects to the SQL 2005 Database server using the above account.3. Vendor can restore using TSQL command pointing to the UNC share on the Application Server which contains a ".bak" which they have provided.The problem is once the restore completes all existing users that were originally setup on the Blank DB are gone, thus preventing the vendor from accessing the DB.What can be done to prevent the users from being removed from the DB or is there a better method of allowing vendor access. Without giving them direct control of the central SQL 2005 Server.Thanks
Answers
Creating the database prior to the restore is meaningless. The restore process will re-create the database (as per the backup) anyhow. This means that the users in the database you created prior to the restore will be wiped, no way around that. Consider creating logins on your SQL Server that matches the users (sid) in your vendor's database. Search for sp_help_revlogin for inspiration.
Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi- Marked As Answer byXiao-Min Tan – MSFTMSFT, ModeratorThursday, November 05, 2009 10:28 AM
- Restore is a pretty high-level operation for a database server. Restore rights imply database creator rights.
The issue with accounts shouldn't be a problem since the restore assigns the restoring account as the new database owner. Once the vendor is back in as the database owner, he can recreate all the logins.
Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP- Marked As Answer byXiao-Min Tan – MSFTMSFT, ModeratorThursday, November 05, 2009 10:28 AM
All Replies
Creating the database prior to the restore is meaningless. The restore process will re-create the database (as per the backup) anyhow. This means that the users in the database you created prior to the restore will be wiped, no way around that. Consider creating logins on your SQL Server that matches the users (sid) in your vendor's database. Search for sp_help_revlogin for inspiration.
Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi- Marked As Answer byXiao-Min Tan – MSFTMSFT, ModeratorThursday, November 05, 2009 10:28 AM
- Restore is a pretty high-level operation for a database server. Restore rights imply database creator rights.
The issue with accounts shouldn't be a problem since the restore assigns the restoring account as the new database owner. Once the vendor is back in as the database owner, he can recreate all the logins.
Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP- Marked As Answer byXiao-Min Tan – MSFTMSFT, ModeratorThursday, November 05, 2009 10:28 AM
