SSHC - Agent-Security-Files-Backups-DBMail.ipynb

{
    "metadata": {
        "kernelspec": {
            "name": "SQL",
            "display_name": "SQL",
            "language": "sql"
        },
        "language_info": {
            "name": "sql",
            "version": ""
        }
    },
    "nbformat_minor": 2,
    "nbformat": 4,
    "cells": [
        {
            "cell_type": "markdown",
            "source": [
                "## SQL Agent Configuration ##\r\n",
                "\r\n",
                "1. Are basic alerts for severity 16 and higher setup and enabled to the correct operator, covering all databases? \r\n",
                "  * If not, recommend running Add Error Event Alerts.sql to enable alert notification. \r\n",
                "  * Consider disabling the severity 20 error alert because of common nuisance emails.\r\n",
                "2. Are the following jobs enabled on the server and completing successfully? \r\n",
                "3. Check Job History on the following jobs to confirm all are succeeding: \r\n",
                "  * Automated Index Maintenance \r\n",
                "  ** Look for any type of similarly-named index maintenance job to ensure index maintenance is being completed routinely. \r\n",
                "  ** Actually check the log table to verify activity and no errors. This or similar: select * from dbaadmin.dbo.indexmaintlog order by id desc \r\n",
                "  * Volume Stats Insert \r\n",
                "  ** Do a select on the table, this should get a few months of data back. Any big jumps or trends? SELECT TOP (1000) * FROM [DBAHOUND].[DBO].[VOLUMESTATS] ORDER BY DATETIMEPERFORMED DESC \r\n",
                "  * Job Failure Notification \r\n",
                "  ** Can also be named “Add Job Failure Notification”\r\n",
                "4. Change the SQL Server Agent log history retention. \r\n",
                "  * Check how far back history goes for important jobs, such as backup jobs. Make sure to change the total row count from default 1000 rows to 10000 or larger, especially on servers that have replication jobs running or multiple large-output jobs, to prevent loss of historical Information. To store hourly transaction log backups rows for 3 months (recommended), you would also need to increase the max rows per job to at least 2160!"
            ],
            "metadata": {
                "azdata_cell_guid": "fe39bb9a-a079-43c9-86c8-1d64c3525f24"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "SELECT TOP (1000) * FROM [DBALOGGING].[DBO].[VOLUMESTATS] ORDER BY DATETIMEPERFORMED DESC"
            ],
            "metadata": {
                "azdata_cell_guid": "c15d6cee-e490-4dcc-aff8-1debef739339"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "Drives\r\n",
                "\r\n",
                "1. Record SQL file locations \r\n",
                "*  Run “select * from sys.master_files” \r\n",
                "** Are there any SQL server files that have been added to the C: drive? If so, they should be moved."
            ],
            "metadata": {
                "azdata_cell_guid": "6821aa1c-3f64-4aa1-b173-c892300bc670"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "select * from sys.master_files"
            ],
            "metadata": {
                "azdata_cell_guid": "d6981874-cdc9-4fbd-b1d5-69f8fa8d8a4c"
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "code",
            "source": [
                "--Volume Stats.sql\r\n",
                "PRINT('Volume Stats Output Window')\r\n",
                "select \r\n",
                "  volume_letter = UPPER(vs.volume_mount_point)\r\n",
                ", volume_name = vs.logical_volume_name\r\n",
                ", file_system_type\r\n",
                ", drive_size_GB = MAX(CONVERT(decimal(19,2), vs.total_bytes/1024./1024./1024. ))\r\n",
                ", drive_free_space_GB = MAX(CONVERT(decimal(19,2), vs.available_bytes/1024./1024./1024. ))\r\n",
                ", drive_percent_free = MAX(CONVERT(decimal(5,2), vs.available_bytes * 100.0 / vs.total_bytes))\r\n",
                "FROM\r\n",
                "   sys.master_files AS f CROSS APPLY\r\n",
                "   sys.dm_os_volume_stats(f.database_id, f.file_id) vs --only return volumes where there is database file (data or log)\r\n",
                " GROUP BY vs.volume_mount_point, vs.file_system_type, vs.logical_volume_name\r\n",
                " ORDER BY volume_letter "
            ],
            "metadata": {
                "azdata_cell_guid": "8141ff23-f29b-4e42-8b9c-06e3581f5be4",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Disaster Recovery ##\r\n",
                "1.\tReview current backup status, look for missing FULL or missing TLOG backups for DB’s not in SIMPLE.\r\n",
                "  *\t**Backup history.sql**\r\n",
                "2.\tRetention settings\r\n",
                "  *\tIf local backups are being taken, view the backup folder and verify that retention policy is deleting old backups.\r\n",
                "  *\tVerify that backup chains are intact - .bak’s should not be deleted before dependent .dif and .trn files."
            ],
            "metadata": {
                "azdata_cell_guid": "4ba7fb95-93ce-4b96-a5d6-f79490d5ea83"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Backup hisotry.sql\r\n",
                "--Looks for a complete backup history, displaying the latest backup of each type.\r\n",
                "\r\n",
                "--See also toolbox/multiserver backup history.sql for health checks.\r\n",
                "PRINT('Backup History Output Window')\r\n",
                "use master\r\n",
                "go\r\n",
                "--sql2012 and above\r\n",
                "select \r\n",
                "\tdatabase_Name\r\n",
                "\t, backuptype \r\n",
                "\t, d.recovery_model_desc\r\n",
                "\t, BackupDate = MAX(BackupDate)\r\n",
                "\t, d.state_desc\r\n",
                "\t, d.is_read_only\r\n",
                "\t, dm.Replica_Role\t\t--SQL 2012+\r\n",
                " from sys.databases d\r\n",
                " inner join \r\n",
                " (\r\n",
                "select distinct \r\n",
                "\tdatabase_name\r\n",
                "\t, database_id = db_id(database_name)\r\n",
                "\t, backuptype = case type\tWHEN 'D' then 'Database'\r\n",
                "\t\t\t\t\t\t\tWHEN 'I' then 'Differential database'\r\n",
                "\t\t\t\t\t\t\tWHEN 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\tWHEN 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\tWHEN 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\tWHEN 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\tWHEN 'Q' then 'Differential partial' END\r\n",
                "\t, BackupDate\t=\tMAX(backup_finish_date)  \t\r\n",
                "\tfrom msdb.dbo.backupset bs\t\t\t\t\t\t\t\r\n",
                " group by Database_name, type\r\n",
                " UNION \r\n",
                " select distinct\r\n",
                "\tdb_name(d.database_id)\r\n",
                "\t, d.database_id\r\n",
                "\t, backuptype = 'Database'\r\n",
                "\t, null\r\n",
                "\tFROM master.sys.databases d\r\n",
                " UNION\r\n",
                " select distinct\r\n",
                "\tdb_name(d.database_id)\r\n",
                "\t, d.database_id\r\n",
                "\t, backuptype = 'Transaction Log'\r\n",
                "\t, null\r\n",
                "  FROM master.sys.databases d\r\n",
                "  where d.recovery_model_desc in ('FULL', 'BULK_LOGGED')\r\n",
                " ) a\r\n",
                " on d.database_id = a.database_id\r\n",
                " \r\n",
                " --SQL 2012+\r\n",
                " LEFT OUTER JOIN ( SELECT  database_id \r\n",
                "\t\t\t\t\t\t ,\tReplica_Role\t\t= CASE WHEN database_state_desc IS NOT NULL and last_received_time is null THEN 'PRIMARY '\r\n",
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHEN database_state_desc IS NOT NULL and last_received_time is not null THEN 'SECONDARY' \r\n",
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tELSE null END\r\n",
                "\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tfrom sys.dm_hadr_database_replica_states) dm\r\n",
                "\t\t\t\t\t\t on dm.database_id = a.database_id\r\n",
                "WHERE database_name not in ('model','tempdb')\r\n",
                "and not (backuptype = 'transaction log' and recovery_model_desc = 'SIMPLE')\r\n",
                "group by database_name, backuptype, d.recovery_model_desc, d.state_desc, d.is_read_only, dm.replica_role\r\n",
                "order by backuptype, recovery_model_desc, database_name asc\r\n",
                "go\r\n",
                "\r\n",
                " /*\r\n",
                "--for SQL 2000 and above\r\n",
                "select distinct \r\n",
                "\t  database_name\t= d.name \r\n",
                "\t, a.backuptype\t\r\n",
                "\t, RecoveryModel\t=\tdatabasepropertyex(d.name, 'Recovery')  \r\n",
                "\t, BackupDate\t=\tMax(a.backup_finish_date)  \r\n",
                "\tfrom master.dbo.sysdatabases d\r\n",
                "\tleft outer join \r\n",
                "\t(\t\tselect distinct \r\n",
                "\t\t\tdatabase_name\r\n",
                "\t\t\t, backuptype = case type\tWHEN 'D' then 'Database'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'I' then 'Differential database backup'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\t\t\tWHEN 'Q' then 'Differential partial' END\r\n",
                "\t\t\t, backup_finish_date\t=\tMAX(backup_finish_date)  \t\r\n",
                "\t\t\tfrom msdb.dbo.backupset bs\t\t\t\t\t\t\t\r\n",
                "\t\t group by Database_name, type\r\n",
                "\t\t UNION \r\n",
                "\t\t select distinct\r\n",
                "\t\t\t  d.name\r\n",
                "\t\t\t, backuptype = 'Database'\r\n",
                "\t\t\t, null\r\n",
                "\t\t\tFROM master.dbo.sysdatabases d\r\n",
                "\t\t UNION\r\n",
                "\t\t select distinct\r\n",
                "\t\t\t  d.name\r\n",
                "\t\t\t, backuptype = 'Transaction Log'\r\n",
                "\t\t\t, null\r\n",
                "\t\t  FROM master.dbo.sysdatabases d\r\n",
                "\t\t  where databasepropertyex(d.name, 'Recovery') in ('FULL', 'BULK_LOGGED')\r\n",
                "  ) a\r\n",
                "\ton d.name = a.database_name\r\n",
                " group by d.name , backuptype ,\tdatabasepropertyex(d.name, 'Recovery')\r\n",
                "order by backuptype, RecoveryModel, BackupDate asc\r\n",
                " */\r\n",
                " \r\n",
                "--granular backup history\r\n",
                "SELECT TOP 1000\r\n",
                "\t\tbs.database_name\r\n",
                "\t, backuptype = CASE \r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 0 then 'Full Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 1 then 'Full Copy-Only Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'I' then 'Differential database backup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'Q' then 'Differential partial' END + ' Backup'\r\n",
                "\t, bs.recovery_model\r\n",
                "\t, BackupStartDate = bs.Backup_Start_Date\r\n",
                "\t, BackupFinishDate = bs.Backup_Finish_Date\r\n",
                "\t, LatestBackupLocation = bf.physical_device_name\r\n",
                "\t, backup_size_mb\t\t\t=\tbs.backup_size / 1024./1024.\r\n",
                "\t, compressed_backup_size_mb =\tbs.compressed_backup_size /1024./1024.\r\n",
                "\t, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on. \r\n",
                "\t, checkpoint_lsn\r\n",
                "\t, begins_log_chain\r\n",
                "\tFROM msdb.dbo.backupset bs\t\r\n",
                "\tLEFT OUTER JOIN msdb.dbo.[backupmediafamily] bf\r\n",
                "\ton bs.[media_set_id] = bf.[media_set_id]\r\n",
                "\tWHERE bs.backup_start_date > dateadd(mo, -1, getdate())\r\n",
                "\t--and database_name = 'w' --optionally filter by database\r\n",
                "\tORDER BY  bs.database_name asc, bs.Backup_Start_Date desc;\r\n",
                " \r\n",
                "\r\n",
                " \r\n",
                " /*\r\n",
                "  --Latest Restore\r\n",
                " select d.name, Latest_Restore = max(restore_date)\r\n",
                "\tfrom sys.databases d\r\n",
                "\tLEFT OUTER JOIN msdb.dbo.restorehistory rh on d.name = rh.destination_database_name\r\n",
                "\tgroup by d.name\r\n",
                "\torder by Latest_Restore desc\r\n",
                "\r\n",
                "*/\r\n",
                "\r\n",
                "\r\n",
                "--Look for backups to NUL, a sign that someone doesn't know what they're doing to the tlog. (Probably VEEAM. Bad VEEAM.)\r\n",
                "--This is bad. Backup to NUL is just truncating the log without backing up the log, breaking the tlog chain. Any subsequent tlog backups are broken until a FULL backup restarts a valid chain.\r\n",
                "--Do not allow VEEAM or other VSS-based backup solutions to do backups to NUL. \r\n",
                "--In VEEAM, this is somewhere near the \"application aware backups\" or similar settings menu in various settings. Disable this. \r\n",
                "SELECT \r\n",
                "\t  bs.database_name\r\n",
                "\t, backuptype = CASE \r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 0 then 'Full Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'D' and bs.is_copy_only = 1 then 'Full Copy-Only Database'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'I' then 'Differential database backup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'L' then 'Transaction Log'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'F' then 'File or filegroup'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'G' then 'Differential file'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'P' then 'Partial'\r\n",
                "\t\t\t\t\t\t\tWHEN bs.type = 'Q' then 'Differential partial' END + ' Backup'\r\n",
                "\t, bs.recovery_model\r\n",
                "\t, BackupStartDate = bs.Backup_Start_Date\r\n",
                "\t, BackupFinishDate = bs.Backup_Finish_Date\r\n",
                "\t, LatestBackupLocation = bf.physical_device_name\r\n",
                "\t, backup_size_mb\t\t\t=\tbs.backup_size / 1024./1024.\r\n",
                "\t, compressed_backup_size_mb =\tbs.compressed_backup_size /1024./1024.\r\n",
                "\t, database_backup_lsn -- For tlog and differential backups, this is the checkpoint_lsn of the FULL backup it is based on. \r\n",
                "\t, checkpoint_lsn\r\n",
                "\t, begins_log_chain\r\n",
                "\tFROM msdb.dbo.backupset bs\t\r\n",
                "\tLEFT OUTER JOIN msdb.dbo.[backupmediafamily] bf\r\n",
                "\ton bs.[media_set_id] = bf.[media_set_id]\r\n",
                "\twhere bf.physical_device_name = 'NUL'\r\n",
                "\tORDER BY  bs.database_name asc, bs.Backup_Start_Date desc;"
            ],
            "metadata": {
                "azdata_cell_guid": "cba32483-c416-4721-8dc4-8b740fe1bf58",
                "tags": []
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Database Mail ##\r\n",
                "1.\tView Database Mail log for failures\r\n",
                "  * Look for unsent mails, unstarted broker, stopped process in Database Mail Diag.sql\r\n",
                "2.\tVerify functionality by sending a test email to managed.sql@sparkhound.com or appropriate distribution group. "
            ],
            "metadata": {
                "azdata_cell_guid": "ecf5be38-fbdb-4146-aaf2-2a75b1d39808"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Database Mail Diag.sql\r\n",
                "PRINT('Databse Mail Diagnostic Output Window')\r\n",
                "SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ; -- should be 1\r\n",
                "EXECUTE msdb.dbo.sysmail_help_status_sp ; --should say STARTED\r\n",
                "--EXECUTE msdb.dbo.sysmail_start_sp --start the database mail queues;\r\n",
                "GO\r\n",
                "\r\n",
                "--Find recent unsent emails, hopefully there are none\r\n",
                "SELECT m.send_request_date, m.recipients, m.copy_recipients, m.blind_copy_recipients\r\n",
                ", m.[subject], sent_account = a.name, m.send_request_user, m.sent_status\r\n",
                ", Error_Description = l.description \r\n",
                "FROM msdb.dbo.sysmail_allitems m\r\n",
                "LEFT OUTER JOIN msdb.dbo.sysmail_account a\r\n",
                "\tON m.sent_account_id = a.account_id\r\n",
                "LEFT OUTER JOIN msdb.dbo.sysmail_event_log AS l  \r\n",
                "    ON m.mailitem_id = l.mailitem_id  \r\n",
                "WHERE\t1=1\r\n",
                "AND     m.send_request_date > dateadd(day, -45, sysdatetime()) -- Only show recent day(s)\r\n",
                "AND\t\tm.sent_status <> 'sent' -- Possible values are sent (successful), unsent (in process), retrying (failed but retrying), failed (no longer retrying)\r\n",
                "ORDER BY m.send_request_date DESC;\r\n",
                "GO\r\n",
                "\r\n",
                "--Send mail test\r\n",
                "--exec msdb.dbo.sp_send_dbmail @profile_name ='hotmail', @recipients ='williamdassaf@hotmail.com', @subject ='test', @body = 'test'\r\n",
                "\r\n",
                "--ALTER DATABASE msdb SET ENABLE_BROKER;"
            ],
            "metadata": {
                "azdata_cell_guid": "111fd831-873e-4d1a-8f09-32e8ce3a8a81",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        },
        {
            "cell_type": "markdown",
            "source": [
                "## Security ##\r\n",
                "1.\tRun **Public Permissions.sql** to identify any SELECT and/or EXECUTE permissions granted to the public server role.\r\n",
                "  *\tDo not recommend granting ALTER, CONTROL, REFERENCES, TAKE OWNERSHIP, VIEW DEFINITION, to anyone who is not already an admin. We should be able to revoke this permission from public with no replacement unless the application is doing something with dynamically creating/altering objects. These permissions shouldn’t be needed by anyone not doing deployments of code changes and shouldn’t be necessary for day-to-day operation unless the application is doing something with dynamically creating/altering objects.\r\n",
                "  *\tNeed developer opinions on how DELETEs, INSERTs, UPDATEs are actually made. Then, grant these DELETE, INSERT or UPDATE permissions to something that is clearly not a read-only user, such as an application service user.\r\n",
                "  *\tIt is not uncommon to grant EXECUTE for a whole database to an application or app service user, but it should not be granted to a read-only user. Perhaps there is a subset of sprocs that are for reporting that a read-only user should have access to EXECUTE, would need developer insight on that.\r\n",
                "  *\tSELECT is the easiest topic. \r\n",
                "  ** There is a SELECT permission granted to public on the entire [dbo] schema. All the individual GRANT SELECT on dbo objects can be revoked and replaced by a single GRANT SELECT ON SCHEMA::[dbo] to one or more application service users including read-only users.\r\n",
                "  ** For GRANT SELECT on objects on other non-dbo schemas, these could be used by read-write application service users and read-only service users. We should replace the public permission with GRANT SELECT on these objects to any user that could access these tables."
            ],
            "metadata": {
                "azdata_cell_guid": "e9cefe5e-7e00-475e-bc10-d4ebbac98541"
            }
        },
        {
            "cell_type": "code",
            "source": [
                "--Public Permissions.sql\r\n",
                "PRINT('Public Permissions Output Window')\r\n",
                "SELECT @@SERVERNAME\r\n",
                "\r\n",
                "\r\n",
                "--Server Level Security\r\n",
                "SELECT rm.state_desc, rm.permission_name, principal_name = QUOTENAME(u.name),  u.type_desc\r\n",
                ",  TSQL = rm.state_desc + N' ' + rm.permission_name + \r\n",
                "\tCASE WHEN e.name is not null THEN ' ON ENDPOINT::[' + e.name + '] ' ELSE '' END +\r\n",
                "\tN' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) + ';'\r\n",
                ",  TSQL = N'REVOKE ' + rm.permission_name +\r\n",
                "\tCASE WHEN e.name is not null THEN ' ON ENDPOINT::[' + e.name + '] ' ELSE '' END +\r\n",
                "\t N' TO ' + cast(QUOTENAME(u.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) + ';', *\r\n",
                "FROM sys.server_permissions rm\r\n",
                "inner join sys.server_principals u \r\n",
                "on rm.grantee_principal_id = u.principal_id\r\n",
                "left outer join sys.endpoints e\r\n",
                "on e.endpoint_id = major_id and class_desc = 'ENDPOINT'\r\n",
                "where u.name not like '##%' \r\n",
                "and u.name = 'public'\r\n",
                "order by rm.permission_name, u.name\r\n",
                "\r\n",
                "\r\n",
                "--Database role membership\r\n",
                "--Multi Database\r\n",
                "declare @TSQL nvarchar(4000) = 'use [?]; \r\n",
                "SELECT DB_NAME();\r\n",
                "SELECT DISTINCT\tQUOTENAME(r.name) as database_role_name, r.type_desc, QUOTENAME(d.name) as principal_name, d.type_desc\r\n",
                ",\tAdd_TSQL = ''EXEC sp_addrolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''\r\n",
                ",\tDrop_TSQL = ''EXEC sp_droprolemember @membername = N'''''' + d.name COLLATE DATABASE_DEFAULT + '''''', @rolename = N'''''' + r.name + ''''''''\r\n",
                "FROM\tsys.database_role_members rm\r\n",
                "inner join sys.database_principals r on rm.role_principal_id = r.principal_id\r\n",
                "inner join sys.database_principals d on rm.member_principal_id = d.principal_id\r\n",
                "where d.name = ''public''\r\n",
                "';\r\n",
                "EXEC sp_MSforeachdb @TSQL\r\n",
                "GO\r\n",
                "\r\n",
                "--Multi-database database permissions\r\n",
                "--script is too long for sp_msforeachdb, had to roll our own.\r\n",
                "\r\n",
                "declare @TSQL varchar(8000) = null, @dbcount int = 0, @x int = 0, @dbname varchar(256) = null\r\n",
                "declare @dblist table (id int not null identity(1,1) primary key, dbname varchar(256)  not null )\r\n",
                "insert into @dblist (dbname)\r\n",
                "select name from sys.databases where name <> 'tempdb' and state_desc = 'ONLINE' \r\n",
                "order by database_id\r\n",
                "select @dbcount = count(dbname) from @dblist\r\n",
                "\r\n",
                "while (@x <= @dbcount)\r\n",
                "BEGIN\r\n",
                "\tselect @dbname = dbname from @dblist d where @x = d.id;\r\n",
                "\r\n",
                "\tselect @TSQL = \t'USE [' + @dbname  + '];\r\n",
                "\tSELECT DB_NAME();\r\n",
                "\tSELECT\tPermission_State_Desc\t=\tperm.state_desc\r\n",
                "\t\t,\tPermission_Name\t\t\t=\tperm.permission_name \r\n",
                "\t\t,\tPermission_Object_Name\t= ISNULL(QUOTENAME(s.name ) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) + CASE WHEN cl.name COLLATE database_default is null THEN '''' ELSE ''.'' + QUOTENAME(cl.name COLLATE database_default) END \t\t\t\r\n",
                "\t\t,\tObject_Type_Desc\t\t=\tobj.type_desc   \r\n",
                "\t\t,\tPrincipal_Name\t\t\t=\tQUOTENAME(u.name COLLATE database_default) \r\n",
                "\t\t,\tUser_Type\t\t\t\t=\tu.type_desc \r\n",
                "\t\t,\tCreate_TSQL = perm.state_desc + N'' '' + perm.permission_name \r\n",
                "\t\t\t+ case when obj.name COLLATE database_default is not null THEN + N'' ON '' + sc.class_desc + ''::'' + ISNULL(QUOTENAME(s.name COLLATE database_default) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) ELSE '''' END \r\n",
                "\t\t\t+ CASE WHEN cl.column_id IS NULL THEN '' '' ELSE ''('' + QUOTENAME(cl.name COLLATE database_default) + '')'' END \r\n",
                "\t\t\t+ N'' TO '' + QUOTENAME(u.name COLLATE database_default)\r\n",
                "\t\t,\tRevoke_TSQL = N''REVOKE '' + perm.permission_name \r\n",
                "\t\t\t+ case when obj.name COLLATE database_default is not null THEN + N'' ON '' + sc.class_desc + ''::'' + ISNULL(QUOTENAME(s.name COLLATE database_default) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) ELSE '''' END \r\n",
                "\t\t\t+ CASE WHEN cl.column_id IS NULL THEN '' '' ELSE ''('' + QUOTENAME(cl.name COLLATE database_default) + '')'' END \r\n",
                "\t\t\t+ N'' TO '' + QUOTENAME(u.name COLLATE database_default) \r\n",
                "\t\t\t, *\r\n",
                "\tFROM sys.database_permissions AS perm \r\n",
                "\tINNER JOIN sys.database_principals AS u\tON perm.grantee_principal_id = u.principal_id\r\n",
                "\tLEFT OUTER JOIN (--https://msdn.microsoft.com/en-us/library/ms188367.aspx\t\t\t\r\n",
                "\t\t\t\t\t\tselect name, object_id, schema_id, is_ms_shipped, class_desc=''OBJECT'', type_desc from sys.objects \r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, 0, null, null, ''DATABASE'', ''DATABASE''  from sys.databases \t\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect  name, schema_id, null, null, ''SCHEMA'', ''SCHEMA'' from sys.schemas\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null,  ''USER'', type_desc from sys.database_principals where type_desc in (''WINDOWS_USER'',''SQL_USER'',''ASYMMETRIC_KEY_MAPPED_USER'',''CERTIFICATE_MAPPED_USER'', ''WINDOWS_GROUP'',''EXTERNAL_GROUPS'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null,  ''USER'', type_desc from sys.database_principals where type_desc in (''WINDOWS_USER'',''SQL_USER'',''ASYMMETRIC_KEY_MAPPED_USER'',''CERTIFICATE_MAPPED_USER'', ''WINDOWS_GROUP'',''EXTERNAL_GROUPS'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null, ''APPLICATION ROLE'', type_desc from sys.database_principals where type_desc in (''APPLICATION_ROLE'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, principal_id, null, null, ''ROLE'', type_desc from sys.database_principals where type_desc in (''DATABASE_ROLE'')\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, assembly_id, null, null, ''ASSEMBLY'', ''ASSEMBLY'' from sys.assemblies \r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, user_type_id, null, null, ''TYPE'', ''USER TYPE'' from sys.types \r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, xml_collection_id, null, null, ''XML SCHEMA COLLECTION'', ''XML SCHEMA COLLECTION'' from sys.xml_schema_collections\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, message_type_id, null, null, ''MESSAGE TYPE'', ''MESSAGE TYPE'' from sys.service_message_types\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, service_contract_id, null, null, ''CONTRACT'', ''CONTRACT'' from sys.service_contracts\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, service_id, null, null, ''SERVICE'', ''SERVICE'' from sys.services\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, remote_service_binding_id, null, null, ''REMOTE SERVICE BINDING'', ''REMOTE SERVICE BINDING'' from sys.remote_service_bindings\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, route_id, null, null, ''ROUTE'', ''ROUTE''  from sys.routes\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name COLLATE database_default, fulltext_catalog_id, null, null, ''FULLTEXT CATALOG'', ''FULLTEXT CATALOG''  from sys.fulltext_catalogs\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, symmetric_key_id, null, null, ''SYMMETRIC KEY'', ''SYMMETRIC KEY''  from sys.symmetric_keys\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, certificate_id, null, null, ''CERTIFICATE'', ''CERTIFICATE'' from sys.certificates\r\n",
                "\t\t\t\t\t\tunion all\r\n",
                "\t\t\t\t\t\tselect name, asymmetric_key_id, null, null, ''ASYMMETRIC KEY'', ''ASYMMETRIC KEY'' from sys.asymmetric_keys\r\n",
                "\t\t\t\t) obj\r\n",
                "\tON perm.major_id = obj.[object_id] \r\n",
                "\tINNER JOIN sys.securable_classes sc on sc.class = perm.class \r\n",
                "\tand sc.class_desc = obj.class_desc\r\n",
                "\tLEFT OUTER JOIN sys.schemas s ON s.schema_id = obj.schema_id\r\n",
                "\tLEFT OUTER JOIN sys.columns cl ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id\r\n",
                "\twhere 1=1\r\n",
                "\tand u.name = ''public''\r\n",
                "\tand perm.major_id > 0\r\n",
                "\t--Ignore internal principals\r\n",
                "\tand u.name COLLATE database_default not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'',''PolicyAdministratorRole'',''ServerGroupReaderRole''\r\n",
                "\t,''ServerGroupAdministratorRole'',''TargetServersRole'',''SQLAgentUserRole'',''UtilityCMRReader'',''SQLAgentOperatorRole'',''dc_operator'',''dc_proxy'',''dc_admin'',''db_ssisadmin'',''db_ssisltduser'',''db_ssisoperator''\r\n",
                "\t,''UtilityIMRWriter'',''UtilityIMRReader'',''RSExecRole'',''DatabaseMailUserRole'')\r\n",
                "\t--Ignore ## principals\r\n",
                "\tand u.name COLLATE database_default not like ''##%##''\r\n",
                "\t--Ignore built-in svc accounts (not recommended anyway!)\r\n",
                "\tand u.name COLLATE database_default not like ''NT SERVICE%''\r\n",
                "\t--Ignore MS shipped internal objects \r\n",
                "\tand (obj.is_ms_shipped = 0 or obj.is_ms_shipped is null) \r\n",
                "\t--Ignore system sprocs (be aware of your naming conventions!)\r\n",
                "\t--and (obj.name not like ''sp_%'' or obj.name is null)\r\n",
                "\t--Ignore SSMS Diagramming Objects\r\n",
                "\tAND (ISNULL(QUOTENAME(s.name ) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) + CASE WHEN cl.name COLLATE database_default is null THEN '''' ELSE ''.'' + QUOTENAME(cl.name COLLATE database_default) END)\r\n",
                "\tNOT IN (''[dbo].[fn_diagramobjects]'',\r\n",
                "\t\t\t''[dbo].[sp_helpdiagrams]'',\r\n",
                "\t\t\t''[dbo].[sp_helpdiagramdefinition]'',\r\n",
                "\t\t\t''[dbo].[sp_creatediagram]'',\r\n",
                "\t\t\t''[dbo].[sp_renamediagram]'',\r\n",
                "\t\t\t''[dbo].[sp_alterdiagram]'',\r\n",
                "\t\t\t''[dbo].[sp_dropdiagram]'')\r\n",
                "\t--Ignore Database Tuning Advisor Objects\r\n",
                "\tAND (ISNULL(QUOTENAME(s.name ) + ''.'','''') + QUOTENAME(obj.name COLLATE database_default) + CASE WHEN cl.name COLLATE database_default is null THEN '''' ELSE ''.'' + QUOTENAME(cl.name COLLATE database_default) END)\r\n",
                "\tNOT IN (''[dbo].[dt_generateansiname]'',\r\n",
                "\t\t\t''[dbo].[dt_adduserobject]'',\r\n",
                "\t\t\t''[dbo].[dtproperties]'',\r\n",
                "\t\t\t''[dbo].[dt_setpropertybyid]'',\r\n",
                "\t\t\t''[dbo].[dt_getobjwithprop]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid]'',\r\n",
                "\t\t\t''[dbo].[dt_setpropertybyid_u]'',\r\n",
                "\t\t\t''[dbo].[dt_getobjwithprop_u]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid_u]'',\r\n",
                "\t\t\t''[dbo].[dt_dropuserobjectbyid]'',\r\n",
                "\t\t\t''[dbo].[dt_droppropertiesbyid]'',\r\n",
                "\t\t\t''[dbo].[dt_verstamp006]'',\r\n",
                "\t\t\t''[dbo].[dt_verstamp007]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid_vcs]'',\r\n",
                "\t\t\t''[dbo].[dt_displayoaerror]'',\r\n",
                "\t\t\t''[dbo].[dt_adduserobject_vcs]'',\r\n",
                "\t\t\t''[dbo].[dt_addtosourcecontrol]'',\r\n",
                "\t\t\t''[dbo].[dt_checkinobject]'',\r\n",
                "\t\t\t''[dbo].[dt_checkoutobject]'',\r\n",
                "\t\t\t''[dbo].[dt_isundersourcecontrol]'',\r\n",
                "\t\t\t''[dbo].[dt_removefromsourcecontrol]'',\r\n",
                "\t\t\t''[dbo].[dt_validateloginparams]'',\r\n",
                "\t\t\t''[dbo].[dt_vcsenabled]'',\r\n",
                "\t\t\t''[dbo].[dt_whocheckedout]'',\r\n",
                "\t\t\t''[dbo].[dt_getpropertiesbyid_vcs_u]'',\r\n",
                "\t\t\t''[dbo].[dt_displayoaerror_u]'',\r\n",
                "\t\t\t''[dbo].[dt_addtosourcecontrol_u]'',\r\n",
                "\t\t\t''[dbo].[dt_checkinobject_u]'',\r\n",
                "\t\t\t''[dbo].[dt_checkoutobject_u]'',\r\n",
                "\t\t\t''[dbo].[dt_isundersourcecontrol_u]'',\r\n",
                "\t\t\t''[dbo].[dt_validateloginparams_u]'',\r\n",
                "\t\t\t''[dbo].[dt_whocheckedout_u]'')\r\n",
                "\torder by Object_Type_Desc, Principal_Name';\r\n",
                "\r\n",
                "\r\n",
                "\texec (@TSQL);\r\n",
                "\tselect @x = @x + 1;\r\n",
                "END"
            ],
            "metadata": {
                "azdata_cell_guid": "7c30ded2-3569-417b-bd06-947812582394",
                "tags": [
                    "hide_input"
                ]
            },
            "outputs": [],
            "execution_count": null
        }
    ]
}