T-SQL: Find Logins Connect Permissions:
SELECT
p.name as 'Name',
Case p.type
When 'S' Then 'SQL login'
When 'U' Then 'Windows login'
When 'G' Then 'Windows group'
When 'R' Then 'Server role'
When 'C' Then 'Login mapped to a certificate'
When 'K' Then 'Login mapped to an asymmetric key' End as 'Type',
Case sp.type When 'COSQ' Then 'CONNECT SQL' End as 'Permission Type',
sp.[state_desc] as 'Permission State',
p.is_disabled AS IsDisabled,
LOGINPROPERTY(p.name, N'IsExpired') AS IsExpired,
LOGINPROPERTY(p.name, N'IsLocked') AS IsLocked,
LOGINPROPERTY(p.name, N'IsMustChange') AS IsMustChange,
CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,
CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,
LOGINPROPERTY(p.name, N'BadPasswordCount') AS BadPasswordCount,
LOGINPROPERTY(p.name, N'BadPasswordTime') AS BadPasswordTime,
LOGINPROPERTY(p.name, N'PasswordLastSetTime') AS PasswordLastSetTime,
LOGINPROPERTY(p.name, N'LockoutTime') AS LockoutTime,
LOGINPROPERTY(p.name, N'DaysUntilExpiration') AS DaysUntilExpiration,
p.create_date AS CreateDate,
p.modify_date AS LastModifieddate
FROM sys.server_principals AS p
LEFT OUTER JOIN sys.server_permissions AS sp ON sp.grantee_principal_id = p.principal_id
LEFT OUTER JOIN sys.sql_logins AS sl ON sl.principal_id = p.principal_id
WHERE
(sp.[type] = N'COSQ' and sp.type is not null)
and p.type not in ('R', 'C', 'K')
And p.name not like '##MS_%' --Filter out Certificate-based SQL Server Logins (https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine)
Order By p.Type ASC, p.name ASC
|
|
|
|
|
|