This article explores the impact of dropping a login in the active directory if that owns a HADR endpoint and SQL Server Always On Availability Groups. It is the 23rd article in the SQL Server Always On Availability Group.
Introduction
Database security is a critical aspect, and it protects you from compromising integrity, availability, and confidentiality. DBA should regularly audit the security logins, their permissions in the SQL instances. It is essential from SOX and PCI audits as well. You should remove the users who left the organization although it is removed from the AD group. You can do the internal security audit every quarterly or yearly.
Usually, DBA creates an AD group for their team members and assigns the sysadmin (highest permissions). Each DBA connects to SQL Server using his credentials and uses Windows authentication for performing database administration activities.
For this article, I assume that [MyDemoSQL\Sunil.g] login exists individually in my primary and secondary replicas, and it has the sysadmin permissions on both replicas.
Suppose one of my DBA friends, Sunil G, did the following tasks in my production environment:
- Configured a two-node SQL Server Always On Availability group
- Used an existing database [SQLShackDemo] for the AG configuration
- Configured a SQL listener for application connectivity
You can use the previous article in this series (TOC at the bottom) for creating a similar environment.
All well here. The SQL Server Always On Availability Group dashboard is healthy. The application works fine after AG failover as well. The AG dashboard is also ready for the failover( failover readiness = No data loss).
Now, the DBA Sunil G moved (resigned) from the organization and his domain id(mydemosql\sunil.g)is deleted from the active directory by AD team. You want to remove this ID from SQL logins as well. Before we drop the login, let’s see it make any impact on the availability group synchronization.
To simulate the issue, I connected to the active directory and dropped the user, as shown below.
- Note: You should not remove a production user from the active directory for testing purpose. You can test the scenario using a lab account
Now, verify the AG dashboard. It looks good. We do not have any impact on dropping the user.
To be sure that it does not put any impact, let’s perform the availability group failover using the failover availability group wizard in SSMS.
- Availabilty group: SQLShackDemoAG
- Current primary replica: SQLNode1\INST
- New primary replica after failover: SQLNode2\INST1
- AG Database: SQLShackDemo
After you move the availability group from the current to the new primary replica, verify the dashboard. It still looks good. AG replica status is synchronized, as shown below.
To further check for any issue after we dropped a user from the active directory, I also restarted the AG endpoint on both replicas, but still, the AG works fine. You can also try to restart SQL Services on both nodes to restart endpoints. We need to stop the endpoint first and start it using the ALTER ENDPOINT command.
|
1 2 3 |
ALTER ENDPOINT Hadr_endpoint STATE = STOPPED GO ALTER ENDPOINT Hadr_endpoint STATE = STARTED |
- Note: You should not restart the endpoint on production unless required. It stops the AG synchronization between the primary and secondary replica, and data could not propagate for AG databases. You get the disconnected status in the AG dashboard as shown below
Now, let’s drop the login from the SQL instances as well. We can expand security and right-click on the login to delete it or use the DROP LOGIN statement.
|
1 2 3 4 |
USE [master] GO DROP LOGIN [MYDEMOSQL\sunil.g] GO |
You cannot drop this SQL login account as shown below. SQL Server complains that this user [mydemosql\suni.g] has granted one or more permissions. We need to drop the permissions of the existing permissions and then drop this server principal.
- Note: If your login is the owner of database objects such as tables, views, stored procedures, you need to transfer those ownerships as well. This article discusses the dependency of login on SQL Server Always On Availability Group objects
- sys.availability_groups: It lists all availability group instances in the primary replica
- sys.availability_replica: It returns the row for each availability group replicas
- You should configure the availability group using a service account. This service account should not be shared with another SQL instance or service
- In case, you use your credentials for configurations, and you should change the endpoint and availability group ownerships to service account or SA
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023
This user does not hold ownership of any database object, but still, we get the error while dropping the login.
Is it related to the SQL Server Always On Availability Group Configuration? Let’s check it out.
Check permissions granted to the server principal [mydemosql\sunil.g] for AG group objects
To check the permissions for a server principal, we can join sys.server_permissions and sys.server_principals as shown in the below query. It filters the records for the specified login.
|
1 2 3 4 5 6 |
USE [master] GO SELECT ssp.class, ssp.class_desc,ssp.[type], ssp.[permission_name], ssp.[state],ssp.state_desc, ssr.[name] AS [owner] FROM sys.server_permissions ssp JOIN sys.server_principals ssr ON ssp.grantor_principal_id = ssr.principal_id WHERE ssr.[name] = N'mydemosql\sunil.g'; |
It shows that the user has connect permissions on the ENDPOINT. In an SQL Server Always On Availability Group configuration, SQL Server creates an endpoint and by default, the connected user in SSMS becomes the owner.
To verify the endpoint name, we join the above query result with the sys.endpoints. It returns all endpoint information in the SQL instance. To get the result, we join on the major_id column of the sys.server_principals and endpoint_id of the sys.endpoints.
|
1 2 3 4 5 6 7 8 9 10 |
USE [master] GO SELECT ssp.class, ssp.class_desc,ssp.[type], ssp.[permission_name], ssp.[state], ssp.state_desc, ssr.[name] AS [owner],e.[name] AS endpoint_name FROM sys.server_permissions ssp JOIN sys.server_principals ssr ON ssp.grantor_principal_id = ssr.principal_id JOIN sys.endpoints e ON ssp.grantor_principal_id = e.principal_id AND ssp.major_id = e.endpoint_id WHERE ssr.[name] = N'mydemosql\sunil.g'; |
As shown below, the endpoint owner is still the [mydemosql\sunil.g]. Due to this endpoint ownership, we cannot drop the login from the specific SQL instance.
This endpoint HADR_ENDPOINT is the default endpoint for the availability group. You can find it in Server Objects -> Endpoints-> Database Mirroring in the SSMS object explorer
To modify the endpoint owner, we can use the ALTER AUTHORIZATION command and specify the new owner. In the below query, we specify the login [MyDemoSQL]ADADMIN] as the new HADR endpoint owner.
|
1 2 3 4 |
USE [master] GO ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO [MyDemoSQL\ADADMIN]; GO |
We also need to give a connect permission on the HADR endpoint for the new account as well.
|
1 2 3 4 |
USE [master] GO GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [mydemosql\adadmin]; GO |
If we try to drop the login now, it still complains about the availability group.
Check SQL Server Always On Availability Group ownership for the [mydemosql\sunil.g]
As per the above screenshot, the user [mydemosql\sunil.g] also has the ownership of the availability group. We can check the AG group ownership using the following dynamic management views (DMV)
|
1 2 3 4 5 6 7 8 9 |
USE [master] GO SELECT ag.[name] AS AvailabiltyGroupName,p.[name] as owner_name ,r.[availability_mode_desc] as [availability_mode] FROM sys.availability_groups ag JOIN sys.availability_replicas r ON ag.group_id = r.group_id JOIN sys.server_principals p ON r.owner_sid = p.[sid] WHERE p.[name] = 'mydemosql\sunil.g' GO |
In the output, it lists the AG replica and its owner. As specified earlier, Sunil configured the [SQLShackDemoAG] availability group with his id.
In the below query, we modify the availability group ownership using the ALTER AUTHORIZATION statement.
|
1 2 3 4 |
USE [master] GO ALTER AUTHORIZATION ON AVAILABILITY GROUP::SQLShackDemoAG TO [mydemosql\adadmin]; GO |
Rerun the query and verify that AG owner is the new login we specified in the above query.
We are now able to drop the Windows user from the SQL login in the primary replica, as shown below.
Now, if we try to drop the login from the secondary replica, it gave a similar error message.
Execute both alter authorization statements on the secondary replica, and you can drop the login.
You should perform the AG failover after dropping the login on both replicas and validate that it does not report any issues. It makes sure you do not have existing issues due to dropping logins.
|
1 2 3 4 5 6 7 8 |
USE [master] GO ALTER AUTHORIZATION ON ENDPOINT::Hadr_endpoint TO [MyDemoSQL\ADADMIN]; GO USE [master] GO ALTER AUTHORIZATION ON AVAILABILITY GROUP::SQLShackDemoAG TO [mydemosql\adadmin]; GO |
Guidelines to configure the SQL Server Always On Availability Group dashboard
We observed that dropping the login did not put any impact on AG health. As per best practice, you should drop the unwanted logins from your SQL instance. Your domain administrator might have removed from the active directory but sill you should remove from the SQL logins. It is useful for your security audit, as well.
Conclusion
In this article, we dropped a login if it is the owner of the SQL Server Always On Availability Group and the HADR endpoint. You should use the scripts in this article to assign authorization to a service account to avoid the issue while dropping the login.
Table of contents
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta