August 24, 2011 at 7:04 am
I have a linked server database that I restore every morning.
My problem is that my remote user account (SVCREMOTE) drops it's access to the newly restored DB every morning. (undertandable the DB was dropped then restored)
So I think I need a script that I can run on a schedule that would give this user (SVCREMOTE) DB owner rights every morning after the restore is complete.
August 24, 2011 at 7:12 am
Looks like you have an orphaned user in your restored database. Remap it after the restore with the ALTER USER command. The syntax is something like this (I'm assuming it's for a Windows login):
USE RestoredDB
ALTER USER DBOwnerUser WITH LOGIN = [MyDomain\DBOwnerUser]
John
August 24, 2011 at 7:19 am
ahhhh..alter...perfect....thanks
August 24, 2011 at 7:24 am
I spoke too soon..
when I run
USE MSPHXX
ALTER USER SVCREMOTE WITH LOGIN = [OURDOMAIN\dbowneruser]
I get
Cannot alter the user 'SVCREMOTE', because it does not exist or you do not have permission.
but it does exist and I have permmission
August 24, 2011 at 7:30 am
Right, so if I understand correctly, you are restoring a database from instance A to instance B? Does the user SVCREMOTE exist in the database on instance A? If not, you will need to use CREATE USER (and sp_addrolemember) instead of ALTER USER. If the SVCREMOTE login is the owner of the database on instance A, then you may need to change the owner on the restored database (for example to sa) and then change it back to SVCREMOTE.
John
August 24, 2011 at 8:02 am
Got it thanks John!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply