SQL_Login_Expired_script (T-SQL Script to find When will a SQL login password expire?)

-- When will a SQL login password expire?

SELECT SL.name AS LoginName
      ,LOGINPROPERTY (SL.name, 'PasswordLastSetTime') AS PasswordLastSetTime
      ,LOGINPROPERTY (SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration
        ,DATEADD(dd, CONVERT(int, LOGINPROPERTY (SL.name, 'DaysUntilExpiration'))
                   , CONVERT(datetime, LOGINPROPERTY (SL.name, 'PasswordLastSetTime'))) AS PasswordExpiration
      ,SL.is_policy_checked AS IsPolicyChecked
      ,LOGINPROPERTY (SL.name, 'IsExpired') AS IsExpired
      ,LOGINPROPERTY (SL.name, 'IsMustChange') AS IsMustChange
      ,LOGINPROPERTY (SL.name, 'IsLocked') AS IsLocked
      ,LOGINPROPERTY (SL.name, 'LockoutTime') AS LockoutTime
      ,LOGINPROPERTY (SL.name, 'BadPasswordCount') AS BadPasswordCount
      ,LOGINPROPERTY (SL.name, 'BadPasswordTime') AS BadPasswordTime
      ,LOGINPROPERTY (SL.name, 'HistoryLength') AS HistoryLength
FROM sys.sql_logins AS SL
WHERE is_expiration_checked = 1
ORDER BY LOGINPROPERTY (SL.name, 'PasswordLastSetTime') DESC

To change all databases to Simple Recovery except system databases

USE MASTER

go
declare
      @isql varchar(2000),
      @dbname varchar(64)
     
      declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
      open c1
      fetch next from c1 into @dbname
      While @@fetch_status <> -1
            begin     
            select @isql = 'ALTER DATABASE @dbname SET RECOVERY simple'
            select @isql = replace(@isql,'@dbname',@dbname)
            print @isql
            exec(@isql)
           
            fetch next from c1 into @dbname
            end
      close c1
      deallocate c1

ORPHAN USERS FIND AND FIX SCRIPT in SQL Server

Orphan Users find and fix: after the migration need to find and if is there any orphan users need to fix

EXEC sp_change_users_login 'Report'

--Use below code to fix the Orphan User issue

DECLARE @username varchar(25)
DECLARE fixusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixusers
FETCH NEXT FROM fixusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END
CLOSE fixusers
DEALLOCATE fixusers

Unable to Restore the backup files in SQL Server

1. Check whether the Backup is valid or not Restore verifyonly from disk = ‘Backup path’

2. We will check the Base Backup is restored first or not

3. Check the LSN sequence 4. Check user has required permissions

5. Check the disk space

6. We will check basing upon the error message.

Services Failed to start in windows

1. SCM – start instance (run: sqlservermanager.msc)

2. OS services (run: services.msc)

3. SAC – Surface Area Configuration (2005)

4. On the command line: Run – net start service name Find the service name in services.msc

5. Using object explorer (Right click on the instance and try to start)

6. Startup parameters have incorrect file path locations. Right click the instance -> Properties -> General Tab -> startup parameters

7. Need to check whether Services are disabled or not Services.msc

8. We will check basing upon the error message