March 23, 2015 at 11:12 am
We log ship our production database tansaction logs to a secondary database on a separate remote domain. Incidentally, this was not set up via the "Ship Transaction Logs" wizard, but instead we had to use the various system stored procedures after restoring a copy of the database on the remote domain in standby mode.
We now have an issue where users are unable to access the remote secondary database without having sysadmin privileges. Clearly we want to avoid this!
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
March 23, 2015 at 11:27 am
Not tried this but could you use a linked server and use the user mapping feature within the linked server to translate a local username to a remote username with sysadmin ?
March 23, 2015 at 2:33 pm
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
That would be a question to your network admins. Create sql server authentication users with read only permissions and associate these users to the windows users
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
db_datareader role already exists in the database. As long as the logins exist ...double click on logins--> go to user mapping page-->select the database-->check db_datareader-->click ok
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
March 24, 2015 at 3:10 am
Ok but how does one create a login and associated user (destined for the secondary database/domain) in the primary database/domain? The Windows NT user or group will not necessarily be available in the primary...
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group 'TEST\test' not found. Check the name again.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply