Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON

By Posted on

Question :

How can i use by making changes to below script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However this code would certainly not be at its best when i need to present for deployment across 300-400 SQL servers with some having always on others not.

Please help me in modifying /check if instance has AG enabled and execute with success

UPDATE As requested by @Kin

SET NOCOUNT ON   

 DECLARE @user_to_be_added sysname  
 SET @user_to_be_added = 'Domainuser'  

 DECLARE @sqltext nvarchar(max) = N''  

     if (select serverproperty('IsHadrEnabled')) = 0

  BEGIN

           -- change  to your user to be added .. make sure that the login is already existing

       SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)

       FROM sys.databases where state_desc ='ONLINE' AND databaseproperty(name,'isReadOnly') = 0 

       SELECT  @sqltext

 END

    Else

 BEGIN


      SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)   

      FROM sys.dm_hadr_availability_replica_states ars
      inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
      where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0  
      and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.

      if  (@sqltext <> '')
       begin
           select @sqltext 
       end
      else 
        begin 
           print 'There is nothing to print - Probably its Secondary Replica of AG'
        end 

 END

Answer :

The code below will execute the queries instead of printing the t-sql.

DECLARE @user_to_be_added varchar(50);
SET @user_to_be_added = 'SQL-DEVTestUser';
DECLARE @sqlstate0 nvarchar(max)

SET @sqlstate0 = '

USE MASTER;

IF EXISTS (select * from syslogins where [name] = ''' + @user_to_be_added + ''')
BEGIN 
PRINT ''LOGIN ' + @user_to_be_added + ' ALREADY  EXISTS'';
END
ELSE
BEGIN
PRINT ''LOGIN ' + @user_to_be_added + ' DOES NOT EXIST'';
CREATE LOGIN [' + @user_to_be_added + '] FROM WINDOWS;
PRINT ''LOGIN ' + @user_to_be_added + ' CREATED'';
END
'
EXEC sp_executesql @sqlstate0


DECLARE @sqlstate1 nvarchar(max)
DECLARE @dbname varchar(100)

DECLARE ForEachDB CURSOR FOR
--query to select databases
SELECT
  [name]--, dbs.state_desc, is_read_only as [ReadOnly], ars.role_desc
FROM sys.databases dbs
LEFT JOIN sys.dm_hadr_availability_replica_states ars
  ON ars.replica_id = dbs.replica_id
WHERE ISNULL(role_desc, '') <> 'SECONDARY'
AND is_read_only = 0
AND dbs.state_desc = 'ONLINE'
--filter out other databases here (master, msdb, etc.)

OPEN ForEachDB

FETCH NEXT
FROM ForEachDB
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @sqlstate1 = '



USE ' + @dbname + ';

IF EXISTS (select * from sysusers where [name] = ''' + @user_to_be_added + ''')
BEGIN 
PRINT ''USER ' + @user_to_be_added + ' ALREADY EXISTS IN DATABASE ' + @dbname + ''';
END
ELSE
BEGIN
PRINT ''USER ' + @user_to_be_added + ' DOES NOT EXIST IN DATABASE ' + @dbname + ''';
CREATE USER [' + @user_to_be_added + '] FOR LOGIN [' + @user_to_be_added + '];
PRINT ''USER ' + @user_to_be_added + ' CREATED IN DATABASE ' + @dbname + '''; 
END

'

  EXEC sp_executesql @sqlstate1
  FETCH NEXT
  FROM ForEachDB
  INTO @dbname
END

CLOSE ForEachDB

DEALLOCATE ForEachDB

My understanding is that you are not approaching this in the right way. you just copy the login using the same sid to all the secondary server. Users get replicated since db is part of AG.

Basically you want to create/replicate logins that are created on primary server to all secondary servers in an AlwaysON availablity group topology.

You can just use dbatools Sync-DbaAvailabilityGroup that will take care of

Syncs depdendent objects for availability groups. Such objects include:

SpConfigure
CustomErrors
Credentials
DatabaseMail
LinkedServers
Logins
LoginPermissions
SystemTriggers
DatabaseOwner
AgentCategory
AgentOperator
AgentAlert
AgentProxy
AgentSchedule
AgentJob

Note that any of these can be excluded.

Edit : Below script will help you :

SET NOCOUNT ON    
DECLARE @user_to_be_added sysname    
DECLARE @sqltext nvarchar(max) = N''  

-- change below to your user to be added .. make sure that the login is already existing
SET @user_to_be_added = 'Domain/Login'  
SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)    
FROM sys.dm_hadr_availability_replica_states ars
inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
 where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0  
and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.

 if  (@sqltext <> '')
 begin
  select @sqltext 
  end
  else 
    begin 
    print 'There is nothing to print - @sqltext is blank'
    end

I see a few issues w/ your script.

  1. There is no T-SQL to create the login.
  2. There is no test to see if the login/user already exists.
  3. If you have standalone dbs on a server that is set up for HADR, the standalone dbs will be skipped by your query.

Assuming the following:

  1. This is a Windows login.
  2. You want to add the login to every server.

  3. You want to add a corresponding database user for the login to every database that meets this criteria:

    a. The database is online
    b. The database is NOT read only
    c. The database is NOT a secondary replica

The script below should generate the dynamic SQL you are looking for. Let me know if this works.

declare @user_to_be_added varchar(50);
set @user_to_be_added = 'SQL-DEVTestUser';
declare @sqlstate0 nvarchar(max)

set @sqlstate0 = '

USE MASTER;

IF EXISTS (select * from syslogins where [name] = '''+@user_to_be_added+''')
BEGIN 
PRINT ''LOGIN '+@user_to_be_added+' ALREADY  EXISTS'';
END
ELSE
BEGIN
PRINT ''LOGIN '+@user_to_be_added+' DOES NOT EXIST'';
CREATE LOGIN ['+@user_to_be_added+'] FROM WINDOWS;
PRINT ''LOGIN '+@user_to_be_added+' CREATED'';
END
'
print @sqlstate0


DECLARE @sqlstate1 NVARCHAR(max)
DECLARE @dbname VARCHAR(100)

DECLARE ForEachDB CURSOR
FOR
--query to select databases
select [name]--, dbs.state_desc, is_read_only as [ReadOnly], ars.role_desc
from sys.databases dbs left join sys.dm_hadr_availability_replica_states ars on ars.replica_id = dbs.replica_id
where isnull(role_desc,'')<>'SECONDARY' and is_read_only = 0 and dbs.state_desc = 'ONLINE'
--filter out other databases here (master, msdb, etc.)

OPEN ForEachDB

FETCH NEXT
FROM ForEachDB
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @sqlstate1 = '



USE '+@dbname+';
go
IF EXISTS (select * from sysusers where [name] = '''+@user_to_be_added+''')
BEGIN 
PRINT ''USER '+@user_to_be_added+' ALREADY EXISTS IN DATABASE '+@dbname+''';
END
ELSE
BEGIN
PRINT ''USER '+@user_to_be_added+' DOES NOT EXIST IN DATABASE '+@dbname+''';
CREATE USER ['+@user_to_be_added+'] FOR LOGIN ['+@user_to_be_added+'];
PRINT ''USER '+@user_to_be_added+' CREATED IN DATABASE '+@dbname+'''; 
END



'

    print @sqlstate1
    FETCH NEXT
    FROM ForEachDB
    INTO @dbname
END

CLOSE ForEachDB

DEALLOCATE ForEachDB


GO

Leave a Reply

Your email address will not be published. Required fields are marked *