Useful Microsoft SQL Scripts

Below is a collection of Microsoft SQL Server Scripts

Find Orphaned User Accounts:

select
    dp.name [user_name]
    ,dp.type_desc [user_type]
    ,isnull(sp.name,’Orhphaned!’) [login_name]
    ,sp.type_desc [login_type]
from   
    sys.database_principals dp
    left join sys.server_principals sp on (dp.sid = sp.sid)
where
    dp.type in (’S’,’U’,’G’)
    and dp.principal_id >4
order by sp.name

Reconnect a SQL User Account in a Database to a SQL Login Account:

ALTER USER LPI_Reporting WITH LOGIN=LPI_Reporting

Check Status of Shrinking a DB:

SELECT
    percent_complete,
    start_time,
    status,
    command,
    estimated_completion_time,
    cpu_time,
    total_elapsed_time
    --,*
FROM
    sys.dm_exec_requests
WHERE
    command = ’DbccFilesCompact’
    
    
Check the Health of a DB:
DBCC CHECKDB