SQLGeeksPro

Hey there, this article is about understanding and resolving the error message  “Error 15141: The Server Principal Owns One or More Availability Groups and cannot be dropped”.

Introduction

First of all let me tell you that the above mentioned error occurs when you try to delete a login in a SQL Server Always On Instance. The error message clearly says that the Login Owns an Availability Group(s). In SSMS the error message looks like as shown in the below screen shot.

Why does the Error 15141: The Server Principal Owns One or More Availability Groups occur in SQL Server?

SQL Server does not allow you to drop any login or Server principal which owns any objects. In this case the server principal is owning an Availability Group. If it allows to drop the login then the Availability Group will become orphaned and that is why SQL Server does not allow to do so.

Now the next question is how come the login has become the owner of the Availability Group.  This is because the login had created the Availability Group while configuring Always On. Does it create any issues as far Always On functionality is concerned? NO, it does not. Then why you need to drop the login?

This is mostly because of the Organization standards. No individual login id should own any Availability Groups. Imagine if the the user leaves the organization and the Id has been removed from Active Directory.

Resolution:

To resolve the error first thing the DBA should do is to identify the list of Availability Groups owned by the login or server principal. Once you have the list, you need to change the ownership to ‘sa’ or any other login as per your Organization Standards. I always say don’t panic and never try to delete the Availability Group itself.

1.Query to Identify the Availability Groups the login Owns:

SELECT ag.[name]  AS availability_groups_name
    ,sp.[name] AS owner_name
    ,ag.group_id
    ,ar.replica_id
    ,ar.owner_sid
FROM  sys.availability_groups ag
JOIN sys.availability_replicas ar ON ag.group_id = ar.group_id
JOIN sys.server_principals sp ON ar.owner_sid = sp.[sid]
WHERE sp.[name]= 'GLOBAL\Dev' -- Change the login name

Sample Output:

Notably the above output shows that the login ‘GlOBAL\Dev’ owns the Availability Group “DEV-AVG01′. Now we’ll change the owner.

2. Query to Change the Availability Group Owner:

 USE [master]
 GO
 ALTER AUTHORIZATION ON AVAILABILITY GROUP::[DEV-AVG01] TO [sa];
 GO

Now again run the Select query to check the Ownership. Once confirmed you can try dropping the login id.

3. Drop the Login:

 USE [master]
 GO
 DROP LOGIN [GLOBAL\Dev]  --Change the login 
 GO

Summary

To conclude, the Error 15141: The Server Principal Owns One or More Availability Groups occur in SQL Server is one of the common errors DBAs encounter while working on SQL Server Always On Instances. Specifically the error occurs when you try to delete a login or server principal which owns one or more availability Groups. While troubleshooting, first thing to remember is not to take any hasty decision. Particularly in Production. The DBA must first list out the Availability Groups owned by the login and then change the ownership to a different login id as per Organization standards which will resolve the issue.

You can also refer the following articles if you are facing Error 15141: The Server Principal Owns One or More Endpoints 

Or Error 15434: Could not drop login as the user is currently logged in

Hey there, the other day I got a call from a DBA saying he is not able to drop a login from a SQL Instance and getting the following error:
“Error 15434: Could not drop login as the user is currently logged in.”

In this blog post, we will discuss what this error message means, why it occurs and how to resolve it. First of all let’s see how the error message looks like SSMS.

 

This error message says database administrator tries to drop a login who is doing some work/running transactions in the SQL Server. First thought came to the DBA’s mind is to wait until the login/user disconnects to the database before they can drop it.

Why does Error 15434 occur in SQL Server?

The error occurs because you are trying to delete/drop a login which has a session (Active/Inactive) in the SQL Instance. The SQL server does not allow to drop the login because it would terminate the session. This can cause business impact.

Resolution:

To resolve Error 15434, the database administrator must first identify the connected session and then either wait for the session to be disconnected or manually disconnect the session. Always check with the user if possible. If it is more or like a generic ID like the above example (‘Admin’), it becomes little tricky.

When I got the call, first thing I checked with the DBA if he is trying to delete his own id. This situation may also occur when you Installed SQL Server and added your individual id and as part of the cleaning process you want to delete your id.

Point often overlooked is that you can not delete your own id. In that case you need to connect either using “sa” account or another “security admin” and “processadmin” (to be able to kill the process) or sysadmin account. If you don’t have another such account then ask your fellow DBA(s) to follow the below steps. Obviously SQL Server does not allow to kill your own session.

1. Identify the connected sessions:

 
USE master
GO 
SELECT spid, blocked  AS BlockedBy, loginame  AS LogInName, login_time,
last_batch, status
FROM   sys.sysprocesses
WHERE loginame = 'Admin'   --Change the loginID you are trying to delete

Sample Output:

Now few points to observe from the sample output. You got few sessions which for the login and they are in sleeping status. You need to to also observe the login_time and the last_batch which is basically the last activity time completed with the connection. If both of them are old, you can decide on killing the sessions. Again be very careful while you do this in production environment.

2. Disconnect or Kill the Sessions:

KILL spid

Screen Shot with above example Spids:

3. Delete the Login:

DROP Login [loginame]

As you can see from the above screen shot the login is dropped without any issue/errors.

Summary:

To conclude, the error “Error 15434: Could not drop login as the user is currently logged in” occurs when you try to delete a login having active/inactive connections to the SQL Instance. The database administrator must first identify and disconnect the connected session, and then drop the login.

You can also refer the below articles related to login drop issues:

Error 15141:The Server Principal Owns One or More Endpoints and cannot be Dropped

Error 15141: The Server Principal Owns One or More Availability Groups

Problem:

As a DBA you may encounter the following error message “The Server Principal Owns One or More Endpoints and Cannot be Dropped. (Microsoft SQL Server Error:15141)” while deleting a login id in Always On SQL server.

This happens because the user (DBA) configured the Always on and configured it. As security best practice we should not keep any individual login id in SQL Instances. All domain logins should be part of the Active Directory group.

The error message:

What is Endpoint in SQL Server:

SQL Server Endpoint is a communication channel that enables clients to connect to the server and access data. It is a database object that defines the ways and means  in which SQL Server may communicate over the network. In SQL Server Always on this endpoint is a special-purpose endpoint that is used exclusively to receive connections from other server instances.

SQL Server will not allow you to drop a user that owns an endpoint because it would disrupt communication for the clients that use the endpoint.

Resolution:

As shown above, the error clearly says that the login owns one or more endpoints. First thing is NOT to have a knee jerk reaction to delete the endpoint itself.

First you need to check the endpoints and their ownership. Second you can change the ownership to “sa” or the domain account as per your organization standards.

Query to to check the Endpoint Ownership:

USE master
GO 
SELECT e.name as  EndpointName,
sp.name AS  EndpointOwner,e.type_desc AS Endpint_Type, e.state_desc  AS Endpoint_Status
FROM  sys.endpoints e
INNER JOIN  sys.server_principalssp
 ON e.principal_id = sp.principal_id

Output Showing  individual login ID owning the Endpoint “Hadr_endpoint”:

Furthermore the endpoint’s name is “Hadr_endpoint” which is the default name of the Availability Group (AG) endpoint. Hence this confirms the individual loginID is part of Always On setup .

Next step is to change the ownership of the endpoint to the login id as per your organization’s standards. In this example I am changing it to “sa”

Here we are using ALTER Authorization statement to transfer ownership. We can not set the ownership to database level users. ALTER AUTHORIZATION is the statement to change the ownership of any entity that has an owner.

Command to change the Endpoint Ownership:

--Replace the login id [sa] with your login.
ALTER Authorization on endpoint ::Hadr_endpoint to [sa]
GO

Now again run the Select query to check the Ownership. Once confirmed you can try dropping the loginid.

Drop LoginID:

DROP Login  [GlOBAL\Tridib.Dev] --Change the loginID you want to delete 

Conclusion:

In summary, By understanding endpoint and why dropping a user with an endpoint is prohibited, you can quickly resolve this issue by transferring the ownership to a standard account and continue with your database administration tasks.

You can refer Error 154934: Could not drop login as the user is currently logged in

Error 15141: The Server Principal Owns One or More Availability Groups

SQL Server orphaned files are a common issue faced by database administrators. These files are no longer in use by the SQL Server database but still exist on the file system. In this blog post, we will discuss the causes of SQL Server orphaned files and the problems they can cause.

Causes of Orphaned Files

There are several reasons why orphaned files can occur in a SQL Server environment. Some of the common causes include:

• Detached databases: When a database is detached from the SQL Server, the files are not automatically removed, leaving behind orphaned files. For example, if a database is detached and the files are not deleted, they will be considered as orphaned files.
• Improperly deleted Databases: This is often overlooked. Whenever you do database decommission, as per best practice first you keep it  offline for a week or so. Once you do not hear anything you go ahead and delete it.

Here is the catch. If you delete the database files while the database is offline, SQL Server do not remove the files automatically.

Let me give you a demo. In the below example I have created a database “AA_Decom”.

Offline Database deletion keeps the physical files

Now let me take the database offline and then delete it.

 

Right Click on the database and Delete:

Let’s Verify if the database and Files are visible to SQL Server. The below screen shot shows they are no longer available.

However the below screen shot shows the physical files still exists in the data and log file path. This is one of the main reasons of having SQL Server Orphaned files.

• Upgrading or Migration: During the process of upgrading or migrating to a new version of SQL Server, if the files are not properly transferred or handled, it can lead to orphaned files.

Consequences of Orphaned Files

Orphaned files in SQL Server can lead to a number of negative consequences. Some examples of the consequences of orphaned files include:

• Lack of Disk Space: Orphaned files can take up valuable disk space on the server, which can lead to disk space issues. For example, if the disk space is full and the SQL Server cannot write to the transaction log, it can cause the database to become unavailable.
• Difficulty in Identifying and Removing Unnecessary Files: These files can also cause confusion when trying to identify which files are in use and which are not, making it difficult to properly manage and maintain the database. For example, if the DBA has to go through hundreds of files to identify which are in use and which are not, it can take a lot of time and resources.
• Security Risks: Orphaned files can also pose a security risk. For example, if an orphaned file contains sensitive information, it could be accessed by unauthorized users.

It’s important to keep an eye on the orphaned files and take necessary actions to remove them, in order to avoid these consequences. Let me know if you have observed any other scenarios by putting your comments below.

You may also like to go through below articles:

Top 5 Tips for Speeding Up SQL Server Backups

Tips to Improve SQL Database Restore Performance

How to Force Manual Failover AlwaysOn Group

 

Have you ever wondered how SQL Agent Job Schedule Got Changed Automatically for one job while you changed the schedule of another job.

Problem Summary:

Your team got a requirement to change the schedule of a job to fix the conflict with another job/process. Team changed the schedule, but another job starts failing/taking longer to complete. Now you are troubleshooting. After scratching your head for quite sometime you realized that it is running on a different schedule. You checked with your team and got confirmation that nobody changed the schedule of the this job.

How SQL Agent Job Schedule Got Changed Automatically:

First thing to check is all recent changes on the SQL Instances. Important point to realize is no matter how minor change you are doing in production environment, you should always have a change task to track.

Straightaway you found a change task performed to create new job. Now this is first clue to check the activity. In our case we found that script of another job was used in the SQL Instance to create the new Job.

Now let’s see what happens when you script out a job. In the following demo setup I have one job named “Agent Job A”. Will script out the job and create another job “Agent Job B”.

 

Right Click on the job and script out:

 

The below highlighted portion of the job code is for Schedule.

Let’s see what happens if we just change the job name in the script to create another job and then change the schedule of the new job.

Execute the script and you can see another job is created with name “Agent Job B”

Now let’s check the schedule of both the jobs. It should be same as we haven’t changed anything.

You can get the script from the following link Script To List SQL Agent Job Schedule to get the output as above. As you can see both jobs have exactly same schedule. Now let’s manually change the schedule of  “Agent Job B” to run only on Saturday as an example.

Right Click on the job “Agent Job B” > Go to Properties > Schedules > Edit

After making the change click on Ok and then again Ok. Now execute the same query again to check the schedules:

You can see “Agent Job A” schedule also same without even touching the job. Continue reading to know the reason, fix and how to prevent it.

Continue >>