Drop Login and User

-- 殺掉DB裏所有的相關帳號.殺之前要先解除該帳號相關連的作業,如AGENT...

-- Drop Login and User
DECLARE @account sysname = 'dbaccount'
DECLARE @dbname sysname
DECLARE @s1 varchar(max)
BEGIN
SET @s1 = 'USE master; DROP LOGIN ['+@account+']; '
EXEC (@s1)
END

DECLARE dropacc CURSOR FOR SELECT name FROM sys.databases
OPEN dropacc FETCH NEXT FROM dropacc INTO @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 =
'USE '+@dbname+'; DROP USER ['+@account+']; '
EXEC (@s1)
FETCH NEXT FROM dropacc INTO @dbname
END
CLOSE dropacc
DEALLOCATE dropacc

-- 參考:我自己

List DB account Permission

-- 已授與予權限的資料
SELECT l.name as [Grantee_Name],
p.state_desc AS [State_Desc],
p.permission_name AS [Permission_Name]
FROM sys.server_permissions AS p 
JOIN sys.server_principals AS l 
ON p.grantee_principal_id = l.principal_id 
WHERE l.name NOT LIKE '##%' AND l.principal_id > 10
ORDER BY 1
GO

-- 角色及其成員資料
SELECT srm.role_principal_id AS [Role_Principal_ID],
sp.name AS [RoleName],
srm.member_principal_id AS [Member_Principal_ID],
member.name AS [MemberName]
FROM sys.server_role_members AS srm
JOIN sys.server_principals AS sp
ON srm.role_principal_id = sp.principal_id
JOIN sys.server_principals AS member
ON srm.member_principal_id = member.principal_id
ORDER BY 1

-- 所有資料庫角色資料
DECLARE @DBName varchar(20)
DECLARE @s1 varchar(max)
CREATE TABLE #DBRole( 
DBName varchar(20), RoleName varchar(50), MemberName varchar(50) )

DECLARE dbrole CURSOR FOR SELECT name FROM [sys].[databases]
OPEN dbrole FETCH NEXT FROM dbrole INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 = 
'INSERT #DBRole '+
'SELECT '''+@DBName+''' AS [DBName], 
role.name AS [RoleName], member.name AS [MemberName] 
FROM '+@DBName+'.sys.database_role_members AS drm 
JOIN '+@DBName+'.sys.database_principals AS role 
ON drm.role_principal_id = role.principal_id 
JOIN '+@DBName+'.sys.database_principals AS member 
ON drm.member_principal_id = member.principal_id 
WHERE member.name NOT IN ( ''dbo'' )'
EXEC (@s1)
FETCH NEXT FROM dbrole INTO @DBName
END
SELECT * FROM #DBRole --WHERE RoleName = 'db_owner' 
DROP TABLE #DBRole
CLOSE dbrole
DEALLOCATE dbrole

-- 參考
-- M$ MSDN

Check Datafile Space

--查詢DB datafile 資料

DECLARE @DBName varchar(50)
DECLARE @s1 varchar(max)
CREATE TABLE #DBFileList ( [DBName] varchar(50), [FileID] tinyint,
[FileType] char(8), [CurrentSize(MB)] int, [FreeSize(MB)] int, 
[FileName] varchar(50), [path] varchar(200), [Status] char(7)) 
DECLARE dbfile CURSOR FOR 
SELECT name FROM sys.databases
OPEN dbfile FETCH NEXT FROM dbfile INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s1 =
'USE '+@DBName+'; '+
'INSERT INTO #DBFileList '+
'SELECT '''+@DBName+''' AS [DBName], 
file_id AS [FileID], 
CASE type WHEN 0 THEN ''DataFile'' 
WHEN 1 THEN ''LogFile'' 
ELSE ''Other'' END AS [FileType], 
size/128 AS [CurrentSize(MB)], 
size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128 AS [FreeSize(MB)], 
name AS [FileName], physical_name AS [Path], state_desc AS [Status] 
FROM sys.database_files; '
EXEC (@s1)
FETCH NEXT FROM dbfile INTO @DBName
END
CLOSE dbfile
DEALLOCATE dbfile
SELECT * FROM #DBFileList ORDER BY 3, 4 DESC
DROP TABLE #DBFileList
-- 參考 我自己

check data and log files grow and shrink log

DECLARE @logpath varchar(200)
SELECT @logpath =
SUBSTRING( [path], 1, LEN([path]) - PATINDEX('%\%', REVERSE([path]) ) )+'/log.trc' FROM sys.traces WHERE is_default = 1

SELECT TOP 100 HostName,
DatabaseID,
DatabaseName,
FileName,
CASE WHEN EventClass = 92 THEN 'Data File Auto Grow'
WHEN EventClass = 93 THEN 'Log File Auto Grow'
WHEN EventClass = 94 THEN 'Data File Auto Shrink'
WHEN EventClass = 95 THEN 'Log File Auto Shrink'
END AS EventClass,
StartTime,
EndTime
FROM fn_trace_gettable( @logpath, default )
WHERE EventClass Between 92 AND 95
ORDER BY StartTime DESC

-- 參考
-- ~楓花雪岳~ 檢查 SQL Server 是否觸發自動成長或自動壓縮