Log Shipping - Retaining SQL Server-user permissions on secondary server

Question

Log Shipping - Retaining SQL Server-user permissions on secondary server

TheGreenShepherd

SSCertifiable

Points: 6979
More actions
May 11, 2011 at 11:14 am

#240086

I created a log shipping schema between two SS2008 servers, opting to leave the secondary database in standby mode, as we would like to run reports off of this. Replication is the direction that we ultimately want to head in, but is infeasible for us right now, due to several SPF's that would adversely affect our web traffic if tables were to be locked during snapshot generation...but I digress.
Everything ran very smoothly with transferring/restoring the backup of this fairly decent-sized (~700GB) database, and the logs are being transferred/restored smoothly as well. The one issue that we've run into is that there were 2 SQL Server accounts (used by our report services) on the primary database which, while they got transfered (insomuch as that I can see them in the database, with datareader roles), they do not truly have access. For obvious reasons, I can't rescript/recreate these db users. The database is accessible/readable to me, the dbadmin role user, just not these two other accounts.
Anyone have any ideas?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

You are using SQLusers that have another internal id on the sqlinstance that hosts your stand-by database.

If these accounts are only supposed to use that database, you could try to drop the accounts at stand-by instance level (not at dblevel !), script them (including sid) on the source server and run the script at the stand-by server.

You could try doing this using this generation script:

SELECT 'CREATE LOGIN [' + loginname + ']'

, ' With PASSWORD ='

, cast(password AS varbinary(256))

,' HASHED , SID = '

, sid

,', DEFAULT_DATABASE=[' + dbname + ']'

,', DEFAULT_LANGUAGE = [' + language + ']'

,', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' /* modify as needed */

FROM syslogins

WHERE isntname = 0

AND name IN ('mydummy')

order by loginname

The goal is to create the sqlaccounts identical, so the database transparency will work because you nolonger need to synchronize the instance sid with the database sid.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

george sibbald SSC Guru Points: 104210 More actions · Answer 2

maybe you just have not transferred the logins?

If you have but created them on the secondary server the user and login now have different sids and the user is orphaned from its login.

google sp_help_revlogin, in the future use that to create the SQL to create your logins on the secondary. For now though use sp_Change_users_login or SQL2008 syntax alter user to synch your users.

---------------------------------------------------------------------

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 3

http://aureus-salah.com/2011/05/10/sql-server-logshipping-issue/

My Blog
www.aureus-salah.com

tnk SSCrazy Points: 2613 More actions · Answer 4

I found a clue to what would work best for my scenario here:

http://www.sqlservercentral.com/Forums/Topic748432-146-1.aspx

I can create the read-only user in the primary, but disable or delete the login on the primary server. Next, allow log shipping to replicate the database read-only user in the standby. Finally, create the login in the standby server making sure to create it with the same SID.

I wish there was a way to do it with the users network credentials but that's ok, this will work fine.

Thanks everyone for your responses.

starunit SSCommitted Points: 1817 More actions · Answer 5

Thanks ALZDBA...

When moving databases I've been using the

EXEC sp_change_users_login @Action='update_one', @UserNamePattern='FQDN\SQLServerUser', @LoginName='FQDN\SQLServerUser';

I never thought of creating the user, at the instance, with the same 'sid'. Brilliant.

ALZDBA (5/11/2011)
You are using SQLusers that have another internal id on the sqlinstance that hosts your stand-by database.
If these accounts are only supposed to use that database, you could try to drop the accounts at stand-by instance level (not at dblevel !), script them (including sid) on the source server and run the script at the stand-by server.
You could try doing this using this generation script:
SELECT 'CREATE LOGIN [' + loginname + ']'
, ' With PASSWORD ='
, cast(password AS varbinary(256))
,' HASHED , SID = '
, sid
,', DEFAULT_DATABASE=[' + dbname + ']'
,', DEFAULT_LANGUAGE = [' + language + ']'
,', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF' /* modify as needed */
FROM syslogins
WHERE isntname = 0
AND name IN ('mydummy')
order by loginname
The goal is to create the sqlaccounts identical, so the database transparency will work because you nolonger need to synchronize the instance sid with the database sid.

Mark
Just a cog in the wheel.

george sibbald SSC Guru Points: 104210 More actions · Answer 6

thats what sp_help_revlogin does for sql authenticated ids, it scripts them out and maintains the sid and password