March 20, 2018 at 8:37 am
Hi,
I have configured an always on cluster, my question is that for security I am creating a user for the connection, but I need this one to have the same permissions in one node as in the other node, in case one of the two fails.how can I do this, since I created the same user in the two nodes and the same password, but the access to the secondary node fails me.
Thanks .
Regards;
March 20, 2018 at 8:45 am
jespinozac1978 - Tuesday, March 20, 2018 8:37 AMHi,I have configured an always on cluster, my question is that for security I am creating a user for the connection, but I need this one to have the same permissions in one node as in the other node, in case one of the two fails.how can I do this, since I created the same user in the two nodes and the same password, but the access to the secondary node fails me.
Thanks .
Regards;
Is this an AlwaysOn availability group setup?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 22, 2018 at 6:47 am
What the error message on failover? You probably have an orphan account and you will need to fix it with the sp_change_users_login command. The problem will reoccur on switch back but you can fix it properly with the ALTER USER command.
Use intergrated security using a domain account and it won’t be an issue.
March 22, 2018 at 7:27 am
Hi,
how did you create both users. If you created them without the same SID, for the sql server the users are different.
You can script the user from server 1 to the server 2 with this stored procedure
Kind regards,
Andreas
March 22, 2018 at 10:48 am
Did you create logins or users without passwords?
March 23, 2018 at 12:14 pm
As stated earlier - you need to create the login on each secondary with the same SID that was created on the primary. When you create the user in the mirrored database, that user is created with the SID defined on the primary and transferred to the secondary.
If the login on the secondary does not have the same SID - then you end up with an orphaned user. When that database is brought online during a failover - the orphaned users would have to be fixed, which would require running sp_change_users_login for each user in each database.
If the users are windows domain account - the SID will be the same and you don't have to worry about orphaned users.
Here is an example script using SQLCMD Mode to create a login on the Primary node - granting access to several databases with specific roles - and then creating the same login on the secondary node.
:connect {Primary}
:out C:\Temp\LoginSID.sql
:setvar Login SqlLoginName
:setvar newPassword ^%$#@!NotNeededAccountAlreadyExists+_)(*&
:setvar uniquePassword +_)(*&ThisLoginWillNeverBeUsed!@#$%^&*()
:setvar databaseRoles ",db_datareader,Execute,View Definition,"
:setvar databaseList ",db1,db2,db3,"
Declare @sqlCommand nvarchar(max);
--==== Create New Login if it does not exist
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = '$(Login)')
Begin
Set @sqlCommand = '
Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(uniquePassword)', char(39)) + '
, default_database = master
, check_expiration = Off
, check_policy = On;';
Execute sp_executeSQL @sqlCommand;
End
--==== If the user does not exist - create it, add user to specified roles
Use db1;
If Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' For Login ' + quotename('$(Login)');
Execute sp_executeSQL @sqlCommand;
Declare @dbName sysname = db_name();
End
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition On schema::dbo To [$(Login)];
End
Go
Use db2;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition On schema::dbo To [$(Login)];
End
Go
Use db3;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition On schema::dbo To [$(Login)];
End
Go
--==== Grab the Login SID and create the login on the destination - if it does not already exist
Set Nocount On;
Declare @LoginSID varbinary(85)
, @sqlCommand nvarchar(max);
Select @LoginSID = sp.[sid]
From sys.server_principals sp
Where sp.name = '$(Login)';
--==== Assume the existing login is correct
Set @sqlCommand = '
Set Nocount On;
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = ' + + quotename('$(Login)', char(39)) + ')
Begin
Create Login ' + quotename('$(Login)') + '
With Password = ' + quotename('$(newPassword)', char(39)) + '
, SID = ' + convert(varchar(85), @LoginSID, 1) + '
, default_database = [master]
, check_expiration = Off
, check_policy = On;
Raiserror(''New Login has been created on %s'', -1, -1, @@servername) With nowait;
End
Declare @LoginSID varbinary(85) = ' + convert(varchar(85), @LoginSID, 1) + ';';
Select @sqlCommand;
Go
:out stdout
:connect {Seconday}
:r C:\Temp\LoginSID.sql
Select sp.name
, sp.[sid]
, sp.create_date
, sp.modify_date
, sp.default_database_name
From sys.server_principals sp
Where sp.[sid] = @LoginSID;
Go
!!del c:\temp\LoginSID.sql
Go
You can do the same thing with a windows domain account - except you don't have to create the login on the primary:
:connect {Secondary}
:setvar Login "domay\user"
:setvar databaseList ",db1,db2,db3,"
:setvar databaseRoles ",db_datareader,Execute,View Definition,"
Use master;
If Not Exists (Select *
From sys.server_principals sp
Where sp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create Login ' + quotename('$(Login)') + ' From Windows With default_database = master';
Execute sp_executeSQL @sqlCommand;
Raiserror('New Login has been created on %s', -1, -1, @@servername) With nowait;
End
Go
:connect {Primary}
Use db1;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
Declare @dbName sysname = db_name();
Raiserror('Created User in database %s', -1, -1, @dbName) With nowait;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition On schema::dbo To [$(Login)];
End
Go
Use db2;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
Declare @dbName sysname = db_name();
Raiserror('Created User in database %s', -1, -1, @dbName) With nowait;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition On schema::dbo To [$(Login)];
End
Go
Use db3;
If charindex(db_name(), '$(databaseList)', 0) > 0
And Not Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
Declare @sqlCommand nvarchar(max);
Set @sqlCommand = 'Create User ' + quotename('$(Login)') + ' With default_schema = dbo';
Execute sp_executeSQL @sqlCommand;
Declare @dbName sysname = db_name();
Raiserror('Created User in database %s', -1, -1, @dbName) With nowait;
End
Go
If Exists (Select *
From sys.database_principals dp
Where dp.name = '$(Login)')
Begin
If charindex('db_datareader', '$(databaseRoles)', 0) > 0 Alter Role db_datareader Add Member [$(Login)];
If charindex('Execute', '$(databaseRoles)', 0) > 0 Grant Execute On Schema::dbo To [$(Login)];
If charindex('View Definition', '$(databaseRoles)', 0) > 0 Grant View Definition On schema::dbo To [$(Login)];
End
Go
This is just a sample - you should modify this as appropriate for your environment.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply