SQL - List of accounts and database permissions

SQL - List of accounts and database permissions

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

From <https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2044d912-72b2-4859-af58-d089284a7120/query-to-see-all-databases-to-which-a-user-has-access?forum=sqlsecurity> 

SQL - List Linked Servers and Logins

SQL - List Linked Servers and Logins

Probably need sysadmin role for this. Copied from the internet, need to find refernce.

SELECT    ss.name 
          ,ss.product 
          ,ss.provider 
          ,'Local Login ' = case sl.uses_self_credential 
                            when 1 then 'Uses Self Credentials' 
                            else ssp.name 
                            end 
           ,'Remote Login Name' = sl.remote_name 
           ,ss.modify_date 
      FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl 
        ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp 
        ON ssp.principal_id = sl.local_principal_id

SQL - List databases, size, and file location

SQL - List databases, size, and file location

This is based on something I found on the internet 7 years ago. Reference unkown

SELECT
    D.name,
    
    F.Name AS FileType,
F.type_desc,
    F.physical_name AS PhysicalFile,
    F.state_desc AS OnlineStatus,
    CAST((F.size*8)/1024 AS VARCHAR(26)) + ' MB' AS FileSize,
    CAST(F.size*8 AS VARCHAR(32)) + ' Bytes' as SizeInBytes
FROM 
    sys.master_files F
    INNER JOIN sys.databases D ON D.database_id = F.database_id
ORDER BY
    D.name

SQL - List Databases

SQL - List Databases

Probably need sysadmin server role to see all databases?

select * from sys.databases

SQL - List Active Connections

SELECT
    spid,
    sp.[status],
    loginame [Login],
    hostname, 
    blocked BlkBy,
    sd.name DBName, 
    cmd Command,
    cpu CPUTime,
    memusage Memory,
    physical_io DiskIO,
    lastwaittype LastWaitType,
    [program_name] ProgramName,
    last_batch LastBatch,
    login_time LoginTime,
    'kill ' + CAST(spid as varchar(10)) as 'Kill Command'
FROM master.dbo.sysprocesses sp 
JOIN master.dbo.sysdatabases sd ON sp.dbid = sd.dbid
WHERE sd.name NOT IN ('master', 'model', 'msdb') 
--AND sd.name = 'db_name' 
--AND hostname like 'hostname1%' 
--AND loginame like 'username1%'
ORDER BY spid