February 19, 2010 at 1:57 pm
tke402-837787 (2/19/2010)
Thanks SSC-EnthusiasticI ran the script on the secondary server master db and received a bunch of errors:
Terminating this procedure. The User name 'db_accessadmin' is absent or invalid.
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'db_owner' is absent or invalid.
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'DOMAIN\GROUP' is absent or invalid.
I then ran it against the replicated db and I get the same error messages except the additional mention that the db is in read only mode:
Terminating this procedure. The User name 'db_accessadmin' is absent or invalid.
Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 108
Terminating this procedure. The User name 'db_owner' is absent or invalid.
Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 123
Failed to update database "DB" because the database is read-only.
That will break the log shipping. Not sure if the OP wants to do that.
February 19, 2010 at 2:04 pm
If the database is log shipped then you will not be able to run SP_CHANGE_USERS_LOGIN as this requires the database to be online for writes, a logshipped db is either in standby mode or readonly so you can't use this.
you have to create the login on the secondary, which is only every going to have read access on the logshipped db unless it is recovered, with the same SID that it has on the primary server. (If the DB is in standby I don't think you can access it.)
These KB articles may hep you do this:
http://support.microsoft.com/kb/918992
http://support.microsoft.com/kb/246133
Gethyn Elliswww.gethynellis.com
February 23, 2010 at 6:56 am
Thanks for the responses. I do notice the SID is different on the secondary server than on the primary even though the login names are the same etc. So since the secondary DB is read only and part of a replication is the fix for this to redo the log shipping replication but to also delete the user from the secondary DB and transfer it over prior to re-configuring the log shipping?
Thanks,
TKE402
February 23, 2010 at 1:57 pm
Extract the login and sid information from the primary server through sp_help_revlogin, drop the sql server login on the secondary server and recreate the login with the login information that is extracted from primary server.
After doing this sql login will be able to access the reporting database and if the login has access to other databases then those databases will not be accessible for the reason user becomes orphaned as SID is brought from primary to secondary.
To get rid of orphaned users in other databases run sp_change_users_login, hope this will solve your issue.
February 23, 2010 at 2:41 pm
murthykalyani has hit the nail on the head, this is what I was trying to across in my previous post.
Gethyn Elliswww.gethynellis.com
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply