SELECT*

How to transfer logins and passwords between instances of SQL Server And if we want to migrate server level role permissions too then we can use below script. SELECT 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''' ' AS [-- Server Roles the Logins Need to be Added --] FROM master.sys.server_role_members SRM JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id WHERE SL.type IN ('S','G','U') AND SL.name NOT LIKE '##%##' AND SL.name NOT LIKE 'NT AUTHORITY%' AND SL.name NOT LIKE 'NT SERVICE%' AND SL.name <> ('sa'); SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END     + ' ' + SrvPerm.permissio

We can use this query to check when the last log has been committed in Secondary replica in Alwasy on. SELECT AGS.NAME AS AGGroupName     ,AR.replica_server_name AS InstanceName     ,HARS.role_desc     ,DRS.synchronization_state_desc AS SyncState     ,DRS.last_hardened_time     ,DRS.last_redone_time     ,((DRS.log_send_queue_size)/8)/1024 QueueSize_MB ,datediff(MINUTE, last_redone_time, last_hardened_time) as Latency_Minutes FROM sys.dm_hadr_database_replica_states DRS LEFT JOIN sys.availability_replicas AR ON DRS.replica_id = AR.replica_id LEFT JOIN sys.availability_groups AGS ON AR.group_id = AGS.group_id LEFT JOIN sys.dm_hadr_availability_replica_states HARS ON AR.group_id = HARS.group_id     AND AR.replica_id = HARS.replica_id ---One more query for  Depending on which replica was having difficulty communicating, you should query both the primary and secondary for the local replica: select r.replica_server_name, r.endpoint_url, rs.connected_state_desc, rs.last_c