October 27, 2008 at 6:52 am
Carry over ID's from the source, created the ID in the LOGINS, but I can not even login with that ID. Any documentation that I can find so I can read on how to create ID's on the Log Shipped read/only database. I am in SQL Server 2005. HELP!!!!!!!!!!!!!
October 27, 2008 at 7:37 am
I think this might be what you're looking for.
(It says it's for SQL 2000 but the same applies for 2005)
http://support.microsoft.com/kb/321247
Security Configuration On Secondary Server For Standby Databases
If you configure the secondary database in standby mode, you can access this database in read-only state. By restoring the secondary database in this mode, this can provide a means by which to run offline reports, thereby offloading some of the work from the production system. However, for the standby database to support read-only functionality, you may have to apply the same security settings on secondary server. Because the database is in standby state, you cannot even make any modifications for the purposes of configuring security. In this case, you have to create all SQL Server logins with the same SID values on the secondary server. Windows logins automatically retain the same SIDs because the Windows GUID is globally unique, even when using multiple domains.
For additional information about how to create SQL logins with the same SID on different servers, click the following article number to view the article in the Microsoft Knowledge Base:
303722 (http://support.microsoft.com/kb/303722/EN-US/%5B/url%5D) How To Grant Access to SQL Logins on a Standby Database When "guest" User Is Disabled
Understanding the SQL Server Security Model
To completely understand the security implications, it is important to understand the security model that Microsoft implemented in SQL Server 2000. When you create a login, it is added to the syslogins table in the MASTER database. For each database that this newly-added login is provided access to, it is added to the sysusers table in that database. The mapping between syslogins table and sysusers table is on the SID field.
If a user database is moved to a different server, the SID values are carried over from the previous server. Either database security breaks when logins on the second server are not created with the same SID values or if the security is improperly configured because of mismatching SID values.
For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
240872 (http://support.microsoft.com/kb/240872/EN-US/%5B/url%5D) INF: How to Resolve Permission Issues When a Database Is Moved
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply