Prodreport SID Probe in PRODSVR and PRODRPT Instances Instance or Databaseĭata in Table 1 suggests that 1) prodreport is an orphaned user PRODRPT.CorpSales, because its SID does not match any login’s SID, and 2) this orphaned user is originated in source instance PRODSVR, because its SID perfectly matches the SIDs of the both login and user prodreports in PRODSVR. Now let us review all the previous SID probing results shown in Table 1. This unique SID serves as the security context of this login and user pair. The query result above shows identical SID for prodreport login and user in the PRODSVR instance. Query output: prodreport SIDs in PRODSVR instance Get prodreport SIDs in PRODSVR instanceįROM dbo.syslogins l JOIN u With this orphaned user’s SID in mind, let us further check user prodreport’s SID in the PRODSVR instance by running the following query:
Since user prodreport lacks a valid login association in the PRODRPT instance, user prodreport possesses no active roles or permissions in the database as it does in the PRODSVR instance, when looking through SSMS. This SID discrepancy suggests that 1) no relationship exists between user prodreport and login prodreport, 2) prodreport is an orphaned user, and 3) no other login in the PRODRPT instance associates with this user (see above SID report). The query output shows that login prodreport’s SID is “0x2F16A7C2D3A19E4786FB90C455D73E2A”, while the prodreport’s SID is “0x2F16A7C2D3A19E4786FB90C455D73E2B”, in the PRODRPT instance. Query output: SIDs for the login and user prodreport SELECT l.name AS LoginName, l.sid AS LoginSID Get SIDs of the login and user prodreport in PRODRPT instance How can we prove the association (or disassociation) of a login with a user in a database instance? Let us examine the SIDs of login and user prodreport in the PRODRPT instance with executing below query. If no orphaned user exists in the database, no output is returned. For example, the above output shows orphaned user prodreport with an SID “0x2F16A7C2D3A19E4786FB90C455D73E2B”. The first output value is the orphaned user name and the second is the SID of this orphaned user. This report returns one row output that indicates an orphaned user existence in the database.
Now, let us run a user SID discrepancy report against the read-only database CorpSales: SQL Server provides an excellent native tool, SP_CHANGE_USERS_LOGIN, to examine any possible SID discrepancy in a database in question. In SQL Server, a user identifies its login, vise versa, by security identifier (SID). The Query engine fails to access the CorpSales database as the security context of the login differs from the user’s. This error message implies that prodreport is a valid login, but the user prodreport is not. User prodreport fails to access read-only CorpSales in PRODRPT When trying to query data from CorpSales database, an error message arises as shown below.
This newly-created user is then transferred to the CorpSales in the PRODRPT instance, through log shipping.Īlthough the desired user now exists in CorpSales database of the PRODRPT instance, connection to the CorpSales database still fails. The work-around, in this case, is to create a corresponding login and user pair in the production instance. However, a user cannot be directly created in a read-only database. The User prodreport must be added to the CorpSales database in PRODRPT as one of the enhancement requirements. CorpSales is a secondary database via log shipping from a corresponding production database instance (PRODSVR). Due to a reporting feature enhancement, this application now needs to access a read-only database (CorpSales) in the instance as well. ScenarioĪ reporting application routinely accesses a read-write database via a designated application user (prodreport) in a reporting database instance (PRODRPT).
APPLICATION SID DOES NOT MATCH CONDUCTOR SID HOW TO
In this technical note, we will address how to probe and resolve SID discrepancy in a read-only database. Several articles have described the technical details of how to resolve this SID discrepancy or orphaned user issue in read-write databases. In this status, application(s) cannot access the database with this user, as the user lacks a corresponding login to represent it in the instance. A database user, without a corresponding login in the residing instance, is called orphaned user. User(s) in the restored database, inherited from those in the source database, may not associate with any login in the residing destination instance. SID discrepancy may occur when a database is restored from a source instance to a destination instance.
An SQL Server login identifies its corresponding database user(s) by security identifiers (SID).
0 kommentar(er)
