Database Users Without Connect Permission

Before you can do anything with a database user - including dropping it - they need at least the CONNECT permission. This query finds every database principal (SQL users, Windows users, groups, certificates and asymmetric keys) that does not have an explicit CONNECT grant, and prints a ready-to-run GRANT CONNECT TO statement next to each one.

SELECT
	u.name AS [Name],
	u.create_date AS [CreateDate],
	'grant connect to [' + u.name + ']' AS [Script]
FROM
	sys.database_principals AS u
WHERE
	u.type in ('U', 'S', 'G', 'C', 'K')
	and u.name not in ('guest', 'INFORMATION_SCHEMA', 'sys')
	and u.principal_id not in
	(
		select
			dp.grantee_principal_id
		from
			sys.database_permissions AS dp
		where
			dp.type = 'CO' --Connect Permission
	)