user account for permission always on 2016

  • 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;

  • jespinozac1978 - Tuesday, March 20, 2018 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;

    Is this an AlwaysOn availability group setup?

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

    "Ya can't make an omelette without breaking just a few eggs" πŸ˜‰

  • 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.

  • 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

    https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

    Kind regards,

    Andreas

  • Did you create logins or users without passwords?

  • 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