Msg 15173 - Login has granted one or more permission(s). Revoke the permission(s) before dropping the login.

Question:I attempted a  DROP LOGIN  resulting in a Msg 15173  - Login  has granted one or more permission(s). Revoke the permission(s) before dropping the login.

How can I complete the DROP LOGIN ?

IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'domain\loginname')
DROP LOGIN [domain\loginname]
GO

Msg 15173, Level 16, State 1, Line 3
Login 'domain\loginname' has granted one or more permission(s). Revoke the permission(s) before dropping the login.

Answer:

1)      Check to see if this logon only has server level permissions and check to see if this login has granted permissions to another server principal. Use this query to identify the permissions granted.

Select perm.* from sys.server_permissions  perm
INNER JOIN sys.server_principals prin ON perm.grantor_principal_id = prin.principal_id
where prin.name = N'domain\loginname'

2)      The permissions granted will need to be revoked , to allow the DROP LOGIN to complete. The permissions can be granted again by a suitable LOGIN.

Before making any changes , ensure you've checked the SQL Server Security Policy

Read More

SQL Server - Powershell and Failed Logon attempts - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA

SQL Server - List all SQL Server users - SQL Server DBA