Easy script-
To check all logins and permission and other details
To check all logins and permission and other details
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
