Shrinking database in SQL Server

Shrinking database requires to change recovery model from Full to simple.

ALTER DATABASE <database> SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE (<log database>, 5)
GO

ALTER DATABASE <database> SET RECOVERY FULL
GO

Server Login and server roles using matrix in SQL Server

WITH CTE_Role (name,role,type_desc)
AS
(SELECT PRN.name,
srvrole.name AS [role] ,
Prn.Type_Desc
FROM sys.server_role_members membership
INNER JOIN (SELECT * FROM sys.server_principals  WHERE type_desc='SERVER_ROLE') srvrole
ON srvrole.Principal_id= membership.Role_principal_id
RIGHT JOIN sys.server_principals  PRN
ON PRN.Principal_id= membership.member_principal_id WHERE Prn.Type_Desc NOT IN ('SERVER_ROLE') AND PRN.is_disabled =0

UNION ALL

SELECT p.[name], 'ControlServer' ,p.type_desc AS loginType FROM sys.server_principals p
  JOIN sys.server_permissions Sp
   ON p.principal_id = sp.grantee_principal_id WHERE sp.class = 100
  AND sp.[type] = 'CL'
  AND state = 'G' )

SELECT
name,
Type_Desc ,
'Y'    AS 'Public',
CASE WHEN [sysadmin] =1 THEN 'Y' ELSE '' END AS 'SysAdmin' ,
CASE WHEN [securityadmin] =1 THEN 'Y' ELSE '' END AS 'SecurityAdmin',
CASE WHEN [serveradmin] =1 THEN 'Y' ELSE '' END AS 'ServerAdmin',
CASE WHEN [setupadmin] =1 THEN 'Y' ELSE '' END AS 'SetupAdmin',
CASE WHEN [processadmin] =1 THEN 'Y' ELSE '' END AS 'ProcessAdmin',
CASE WHEN [diskadmin] =1 THEN 'Y' ELSE '' END AS 'DiskAdmin',
CASE WHEN [dbcreator] =1 THEN 'Y' ELSE '' END AS 'DBCreator',
CASE WHEN [bulkadmin] =1 THEN 'Y' ELSE '' END AS 'BulkAdmin' ,
CASE WHEN [ControlServer] =1 THEN 'Y' ELSE '' END AS 'ControlServer'
FROM CTE_Role
PIVOT(
COUNT(role) FOR role IN ([public],[sysadmin],[securityadmin],[serveradmin],[setupadmin],[processadmin],[diskadmin],[dbcreator],[bulkadmin],[ControlServer])
) AS pvtWHERE
WHERE name not like '%#%'
AND name not in ('_expressProvisioning','public','sysadmin','securityadmin','serveradmin','setupadmin','processadmin','diskadmin','dbcreator','bulkadmin')
Order by name;

You can edit exception list to remove account that you do not want.

To get list of Server logins and database with default schema per database

Before getting entire list first get all server login list



SELECT name
        FROM SYS.SERVER_PRINCIPALS
        WHERE name NOT LIKE '#%'
        AND name NOT IN ('sa','public','sysadmin','securityadmin',
        'serveradmin','setupadmin','processadmin','diskadmin','dbcreator',
        'bulkadmin','NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
        ORDER BY name

In code below we have to add database name instead <database-name>  and union with all databases same way right now its union with 2 databases.


DECLARE @name SYSNAME

CREATE TABLE #list (databaseName NVARCHAR(100)
        ,loginname NVARCHAR(100)
        ,default_schema_name NVARCHAR(100))

DECLARE    refreshTableCursor CURSOR FOR
        SELECT name
        FROM SYS.SERVER_PRINCIPALS
        WHERE name NOT LIKE '#%'
        AND name NOT IN ('sa','public','sysadmin','securityadmin',
        'serveradmin','setupadmin','processadmin','diskadmin','dbcreator',
        'bulkadmin','NT AUTHORITY\SYSTEM','NT AUTHORITY\NETWORK SERVICE')
        ORDER BY name

    OPEN    refreshTableCursor
    FETCH NEXT FROM refreshTableCursor INTO @name
    WHILE    @@FETCH_STATUS = 0
    BEGIN
        SELECT N'<database-Name>',
                p.name,
                p.default_schema_name
        FROM sys.server_principals AS sp
        LEFT OUTER JOIN <database-name>.sys.database_principals AS p
        ON sp.sid = p.sid
        WHERE sp.name = @name
        UNION ALL
        SELECT N'<database-Name>',
                p.name,
                p.default_schema_name
        FROM sys.server_principals AS sp
        LEFT OUTER JOIN <database-name>.sys.database_principals AS p
        ON sp.sid = p.sid
        WHERE sp.name = @name
    FETCH NEXT FROM refreshTableCursor INTO @name
    END
    CLOSE        refreshTableCursor
    DEALLOCATE    refreshTableCursor   
   
SELECT    LoginName,
        DatabaseName,
        Default_Schema_Name,
        CASE WHEN is_disabled = 0 THEN 1 ELSE 0 END AS Active
FROM #list INNER JOIN sys.server_principals sp
    ON #list.LoginName = sp.name
WHERE loginname IS NOT NULL
    AND default_schema_name IS NOT NULL
ORDER BY loginname

DROP TABLE #list