1

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 = 'Domain\user'  

 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

3 Answers 3

1

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-DEV\TestUser';
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
5

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 
12
  • Thanks Kin, i did upvote for the answer as dbatools is really a good solution. But i am restricted not to use it within company standards due to security constraints for installing any modules from internet for now, unless tested and approved. Though there is a separate job to take care of sync issues, but how can i achieve this with my above scripts as they tend to break for instance running AG. I am looking how can i check in above script to see if AG enabled go ahead and create that may be on its primary as we have process to take care on secondaries. thanks Feb 14, 2019 at 23:34
  • 1
    Why don't you just copy logins with same sid and password hash ? This way you guarantee that there will be no orphaned users when failover happens. On a side note, you can use this link to talk to your security team about dbatools dbatools.io/secure .
    – Kin Shah
    Feb 15, 2019 at 0:31
  • I see that you are just creating windows logins. That will never get orphaned. So just create only logins and you should be good
    – Kin Shah
    Feb 15, 2019 at 0:32
  • Exactly just windows login and their users within in each database as per 2nd script. I will definitely get my security team have a look. Bur for now, pardon for my lack in AG fn that might available to add in existing script to have an additional check if instance is an AG, if yes it needs to make sure its primary replica because then only it creates the logins else it will just fail for secondary. Problem is we need to execute this on list of servers, approx 500, picking one by one where it will break for AG is cumbersome. How can i achieve without breaking script ? Feb 15, 2019 at 1:21
  • I have a solution and will post tomorrow. Thanks.
    – Kin Shah
    Feb 15, 2019 at 2:44
1

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-DEV\TestUser';
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

2
  • Perfect, How can i execute within script if everything looks good after initial review, instead copying and pasting the output to execute Feb 21, 2019 at 17:42
  • You have to replace each PRINT statement with EXEC SP_EXECUTESQL. I also had to remove "go" from inside the cursor. I posted this query below.
    – nsb53
    Feb 21, 2019 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.