Drop sysadmin with ALTER SERVER ROLE and DROP MEMBER

24 December,2014 by Jack Vamvas

Question: How can I delete the “sysadmin” server elevated role from a SQL Server login? 

Answer: There are two standard methods to drop a sql server login from the sysadmin server role.

Method 1: Execute T-SQL.

Using the following code as a template. Apply the SQL Server Login and execute the code against the SQL Server

 

ALTER SERVER ROLE [sysadmin] DROP MEMBER [My Domain\My SQL Server Login]
GO

 

Method 2: Use the SSMS GUI

To delete the alter the SQL Server login privileges.

Alter_server_role

 

Read More on SQL Server Security

ALTER AUTHORIZATION sql to change owner of a database

SQL Server Security Policy

How to create a SQL Server Security Audit

Powershell sql server security audit

 


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


Share:

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 on Drop sysadmin with ALTER SERVER ROLE and DROP MEMBER


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