| Author |
Topic |
|
cornall
Posting Yak Master
148 Posts |
 Posted - 2007-05-31 : 09:48:48
|
| Hi,I have a database that exists on one domain. I then log ship to another domain via FTP. The dtabase is kept in read only mode and logs applied throught the day.I can't use sp_grantdbaccess on the restoreed db as it is ofline/standby mode.Error : Could not run BEGIN TRANSACTION in database 'SDSMAPS' because the database is read-only.I can't use EXEC sp_grantdbaccess N'DOMAIN\user', N'user' on the source DB as it cant see the domain.Error : Windows NT user or group 'DOMAIN\user'not found. Check the name again.How can I get around this? |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-31 : 10:08:17
|
| You can't add user from other domain if domains are not trusted, use sql authentication in this case. |
 |
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-05-31 : 11:04:38
|
| Is there no way to force it? |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-05-31 : 11:09:47
|
| Or to apply the logins at the other end? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-31 : 17:13:01
|
| What do you mean 'forct it' and 'apply the logins at the other end'? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-31 : 17:15:50
|
| If you setup a trust relationship between the two domains, then you will be able to add accounts to the SQL Server from the other domain. So contact the people who administer both domains and have them add a trust relationship. If they are unwilling to do that, then you must use SQL authentication.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-01 : 03:29:06
|
| Would the following work? 1. Restore the DB at their end.2. Bring the DB online.3. Setup the users.4. Restore another backup.5. Leave offline and begin applying logs.6. Use "EXEC sp_change_users_login 'Auto_Fix', 'DOMAIN\user', null" to fix the logins? |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-01 : 03:50:15
|
| Sorry just realised that the users will simply be overwritten by the second restore. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-06-01 : 11:26:02
|
| Even if you can get the users added without an error, the users will not be able to use their accounts due to no trust relationship setup between the domains.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-05 : 06:10:06
|
| Hi,There is no way we can get a trust set up so I am going to have to use simple SQL accounts. I haev another problem.When the restore is complete at the other end they can't run sp_change_users_login "Update_One" as the databse is in read only mode so logs can be applied.How can I get around this as the user at the other end is out of sync.Thanks for all the help so far.Regards David |
|
|
|
cornall
Posting Yak Master
148 Posts |
Posted - 2007-06-05 : 06:51:04
|
| Hi Solved the latest problem by exporting and importing the SQL user as below.http://support.microsoft.com/default.aspx?scid=kb;en-us;246133 |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-06-05 : 10:14:46
|
| You still have to remap db users to sql logins once the db recovered. |
|
|
|
|
sp_grantdbaccess and an ofline database