Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

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

21 August,2013 by Jack Vamvas

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer