January 27, 2014 at 1:46 pm
Hello,
*disclaimer* I am not a DBA and very new to server administrator and T-SQL.
I mulled through some documentation and I have a 2 node HA cluster.
I have a database user "usera" that can connect fine when the primary SQL server is "server1", if it is the other node "server2" it will not allow me to connect to my database "db_a".
So I realized that the problem is that the user "usera" was never created under "server2"> security> logins.
I created the user with the same name and password that resides on "server1" I can now authenticate, but it will now allow me to connect to database "db_a".
If I go to server2 > security >logins>usera>properties>usermappings and select "db_a" from the list, and select db_owner is tells me that it cannot create user "usera" because it already exists.
I feel that I have missed a huge step. Is there a different way to do this? do I create a new user in db_a that usera will map to?
I apologize if my explanation of this is way off and thank you so much for any help.
January 27, 2014 at 1:51 pm
Very common issue. The SIDs for the logins are not the same on both boxes. Try this to generate the login on all standby servers so failovers aren't a headache:
SELECT
'create login [' + p.name + '] ' +
case when p.type in('U','G') then 'from windows ' else '' end +
'with ' +
case when p.type = 'S' then 'password = ' + master.sys.fn_varbintohexstr(l.password_hash) + ' hashed, ' +
'sid = ' + master.sys.fn_varbintohexstr(l.sid) +
', check_expiration = ' + case when l.is_expiration_checked > 0 then 'ON, ' else 'OFF, ' end +
'check_policy = ' + case when l.is_policy_checked > 0 then 'ON, ' else 'OFF, ' end +
case when l.credential_id > 0 then 'credential = ' + c.name + ', ' else '' end
else '' end +
'default_database = ' + p.default_database_name +
case when len(p.default_language_name) > 0 then ', default_language = ' + p.default_language_name else '' end
FROM sys.server_principals p
LEFT JOIN sys.sql_logins l ON p.principal_id = l.principal_id
LEFT JOIN sys.credentials c ON l.credential_id = c.credential_id
WHERE p.type in('S','U','G')
AND p.name <> 'sa'
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 27, 2014 at 2:50 pm
Thank you for your reply.
I'm afraid that I do not understand the syntax of the code. Is there any documentation to go along with it?
January 27, 2014 at 4:28 pm
It is code that creates code - one of my favorite things!
As I covered in my reply to your PM: ALWAYS verify stuff you get from anyone online before you make use of it on a non-throwaway system!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply