April 9, 2010 at 9:15 am
I have created a high-performance mirroring session - PARTNER SAFETY OFF - and a database snapshot on a secondary instance for reporting purposes.
SQL-authentication logins are not mapped to the corresponding users on the snapshot.
How can I map them? Snapshot is readonly.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 9, 2010 at 10:04 pm
First, you have to create the login on the secondary server with the same SID that exists on the primary server. Lookup CREATE LOGIN in BOL for exact syntax, but this will get you started:
Create Login {your login}
With password = 0x01008C59D6E92AA3D8E88E71233F82EAC10CF9C41DBC06A99D96 hashed
,sid = 0x4DA590DCB2E0A24982092F1514332EC3
,check_expiration = Off
,check_policy = On
,default_database = [some database];
To get the password and the SID, you use the following:
Select * From sys.server_principals sp Where name = 'your login';
Select cast(loginproperty('your login', 'PasswordHash') As varbinary(256));
Once you have the login create on the secondary, it should now match with the user on the mirrored database. If you are creating a new user, then the user will exist in the mirror - but won't be available in the snapshot until you refresh/recreate the snapshot.
What I've done in the past was create the login/user on the production machine and disabled the login. Then, create the login on the reporting server with an appropriate password and recreate the snapshot. The login now has access on the reporting server - but cannot login to production.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 10, 2010 at 5:51 am
best way
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2010 at 5:56 am
Jeffrey Williams-493691 (4/9/2010)
First, you have to create the login on the secondary server with the same SID that exists on the primary server. Lookup CREATE LOGIN in BOL for exact syntax, but this will get you started:
Create Login {your login}
With password = 0x01008C59D6E92AA3D8E88E71233F82EAC10CF9C41DBC06A99D96 hashed
,sid = 0x4DA590DCB2E0A24982092F1514332EC3
,check_expiration = Off
,check_policy = On
,default_database = [some database];
To get the password and the SID, you use the following:
Select * From sys.server_principals sp Where name = 'your login';
Select cast(loginproperty('your login', 'PasswordHash') As varbinary(256));
Once you have the login create on the secondary, it should now match with the user on the mirrored database. If you are creating a new user, then the user will exist in the mirror - but won't be available in the snapshot until you refresh/recreate the snapshot.
What I've done in the past was create the login/user on the production machine and disabled the login. Then, create the login on the reporting server with an appropriate password and recreate the snapshot. The login now has access on the reporting server - but cannot login to production.
Thank you for the great info! I will give that a try.
I found a slightly different alternative in http://support.microsoft.com/kb/918992/en-us, but your solution is better because it does not require creating the sp_help_revlogin sproc in master db.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 10, 2010 at 6:15 am
Give this a try
select 'create login ' + name + ' with password = ' +
sys.fn_varbintohexstr(password_hash) + ' hashed, sid = ' +
sys.fn_varbintohexstr(sid) + ', default_database=[' + default_database_name + ']'
from sys.sql_logins
where name = 'club'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2010 at 9:00 am
If you want to create all logins from prod - then sp_help_revlogin would be the way to go. I didn't want all users on the prod system, so I used the above method for each user.
Haven't tried Perry's method - but that should work very well also.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 10, 2010 at 9:14 am
Thanks both for the insightful answers.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
April 10, 2010 at 10:44 am
😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2010 at 10:59 am
In 2008, there's no need to use the undocumented and unsupported fn_varbintohexstr any more.
Simply CONVERT directly to (N)VARCHAR using the new style parameter.
Style 1 preserves the 0x header, style 2 removes it.
Example: CONVERT(VARCHAR(87), sid, 1) produces something like '0x8ADD7B9A5EA6A64A882343F1CB7DD527' (as a string!)
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 10, 2010 at 11:57 am
personally i prefer the function, it works on SQL Server 2005 as well and does exactly what it says on the tin. Its undocumented for a very good obvious reason i would have thought
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 10, 2010 at 9:12 pm
Perry Whittle (4/10/2010)
personally i prefer the function, it works on SQL Server 2005 as well and does exactly what it says on the tin
Personally, I prefer to use documented and supported features wherever possible 😛
Its undocumented for a very good obvious reason i would have thought
What's that?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 11:36 am
Paul White NZ (4/10/2010)
Personally, I prefer to use documented and supported features wherever possible 😛
hmm, yes i'm sure you do 😛
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 11, 2010 at 11:53 am
Perry Whittle (4/11/2010)
hmm, yes i'm sure you do 😛
I feel I am missing something vital here...:unsure:
Is that a joke of some kind?
And what was that very good reason you mentioned? I am intrigued!!!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 11, 2010 at 12:28 pm
As an aside (not really too relevant to this thread), performance is vastly better using CONVERT too.
Using the 2-million row test rig from:
Time using sys.fn_varbintohexstr: 1m 47s
Time using CONVERT: 1s
:w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 12, 2010 at 6:33 am
Paul White NZ (4/10/2010)
Personally, I prefer to use documented and supported features wherever possible 😛
was this some sort of joke then, maybe i am missing something vital, you always like to have the last word though dont you
BTW he is converting 1 login not 2million, bit of a difference there!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply