When to use Update_one and when to use AutoFix

We can use Update_one when the Database user name and login name are different.

AutoFix can be used when both Database user name and login name are same.

And auto fix can be used when login does not exist, if you provide password new login will be created with the provided password, if you have not passed any password, sql server will generate password then creates the new login.

If a login name does not exists, you would have to create it first before doing the mapping. A quick way to do this is to use the following command which will create the login and then map the login to the user.

--Command to map an orphaned user to a login that is not present but will be created

EXEC sp_change_users_login 'Auto_Fix', 'TestUser3', null,'pwd'

GO

Summarizing the T-SQL Used

In the above process, the stored procedure sp_change_users_login is used. The variable [ @Action ] specifies the exact use of this stored procedure. It accepts a parameter as varchar(10) and can have one of the following values:

• If parameter is Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present.
• If parameter is Report, it lists the orphaned users and their security identifiers (SID).
• If parameter is Update_One, it links the specified database user to an existing SQL Server login.

To Get the OS Name and version and also other servers

systeminfo | findstr /B /C:"OS Name" /C:"OS Version"

To get the OS Version of other server:   get-wmiobject win32_operatingsystem -computer TK3ECITXSQL007 | select pscomputername,Version

This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

-- Enabling/Disabling SQL agent jobs on AO instances.

-- <Raghavender Chavva>

-- <20^th March,2014>

-- Last Modified: NA

-- <This query is not suitable for SQL Server versions which are below SQL Server 2012 and SQL Servers which are not in Always On>

--******************************************************************************

--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF

--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED

--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

--* PARTICULAR PURPOSE.

--*

--******************************************************************************

-- < This script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.>

-- Step 1: -- ---checking instance replica status (here Primary)

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='PRIMARY') 

begin

-- Step 2: -- Colleting the job names

select name into #temp from msdb..sysjobs where enabled=0 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history')

-- select * from #temp

declare @jobname varchar(200)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM #temp

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname  

--- Step 3: Enabling the required jobs.

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in primary '+@jobname 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname+''', @enabled = 1' –- Enabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp

END

-- Step 4: -- ---checking instance replica status (here Secondary)

else

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='SECONDARY')

–- Step 5:  Colleting the job names

begin

select name into #temp1 from msdb..sysjobs where enabled=1 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history','_SQL_DBCCCheckDB','_SQL_DbccCheckFG','_SQL_DbccCheckTable','_SQL_DbccCheckCatalog')

--select * from #temp

declare @jobname1 varchar(200)

DECLARE db_cursor CURSOR FOR SELECT name FROM #temp1

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname1 

–- Step 6: Disabling the jobs

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in secondary '+@jobname1 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname1+''', @enabled = 0'

       FETCH NEXT FROM db_cursor INTO @jobname1  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp1

end

this script will verify the SQL Server instance and enabled/disabled the jobs based on the replica server state.

Below is the script with comments, please let me know if I need to add more comments:

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='PRIMARY')   ---checks whether the instance is primary or not

begin

select name into #temp from msdb..sysjobs where enabled=0 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history') –- Colleting the job names

--select * from #temp

declare @jobname varchar(200)

DECLARE db_cursor CURSOR FOR 

SELECT name

FROM #temp

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname  

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in primary '+@jobname 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname+''', @enabled = 1' –- Enabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp

END

else

if((select role_desc from sys.dm_hadr_availability_replica_cluster_states arc,sys.dm_hadr_availability_replica_states ars where arc.replica_id=ars.replica_id and arc.group_id=ars.group_id and replica_server_name=(select @@SERVERNAME))='SECONDARY') ---checks whether the instance is secondary or not

begin

select name into #temp1 from msdb..sysjobs where enabled=1 and name not in('_SQL_BackupTranAll','_SQL_BackupAll','syspolicy_purge_history','_SQL_DBCCCheckDB','_SQL_DbccCheckFG','_SQL_DbccCheckTable','_SQL_DbccCheckCatalog') –- Colleting the job names

--select * from #temp

declare @jobname1 varchar(200)

DECLARE db_cursor CURSOR FOR SELECT name FROM #temp1

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @jobname1 

WHILE @@FETCH_STATUS = 0  

BEGIN  

       print 'Job name in secondary '+@jobname1 

        print 'exec msdb..sp_update_job @job_name = '''+@jobname1+''', @enabled = 0' –- Disabling the jobs

       FETCH NEXT FROM db_cursor INTO @jobname1  

END  

CLOSE db_cursor  

DEALLOCATE db_cursor

drop table #temp1

end

Finding the total disk space and free space where database files resides

SELECT distinct volume_mount_point ,F.name,physical_name

    ,cast(available_bytes as decimal)/ (1024*1024*1024) as [Avaialable_Space_in_GB],cast(total_bytes as decimal)/ (1024*1024*1024) as total_space_in_GB

FROM sys.master_files AS f

CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)

order by F.name

sys.master_files  and sys.dm_os_volume_stats(f.database_id, f.file_id)