SQLGeeksPro

Hi there! In this article we are going to discuss how to resolve the error “The database could not be exclusively locked to perform the operation. (Microsoft SQL Server, Error: 5030)”

The Complete error message while rename fails for exclusive lock looks like below: Refer How To Rename SQL Server Database

Why the Error Message:

You need to have exclusive lock on the database to rename the db. Exclusive lock means there are no other database connections open/using the database. SQL Engine is smart enough not to allow database name change while other users are accessing the database.

Query to Resolve Database could not be exclusively locked:

Following query will switch the database from Multi User mode to Single User mode, then rename the database and then set it back to Multi User mode.

USE master
GO
ALTER DATABASE [DatabaseName]  --Change The Database Name
SET SINGLE_USER  WITH  ROLLBACK  IMMEDIATE
GO
ALTER DATABASE [DatabaseName] MODIFY NAME = [NewDatabaseName]
GO
ALTER DATABASE [NewDatabaseName]  --Put the new database name
SET MULTI_USER WITH ROLLBACK IMMEDIATE

Point often overlooked in the above query is the final statement, where you need to put the new database name.

To demonstrate please find the following Example for database named “SQLGeeksPro” renaming to “SQLGeeksPro_NewName”

Please spread the word:

https://sqlgeekspro.com/database-could-not-be-exclusively-locked/

In this article we are going to discuss how to fix incorrect Mexico Time in SQL Server sys.time_zone_info system catalog.

Problem:

One of our customer informed that Mexico changed their DST . In UAT, application is showing correct time which is UTC -6 however in Production the mexico time zone is showing as UTC – 5. The application is referring the sys.time_zone_info system catalog in SQL Server.

Troubleshooting:

In the first place you should understand what is the actual problem. In other words what the customer is talking about. The problem statement it is not very clear if it is SQL Server issue or OS issue or it is something to do with application code.

After a short call with the customer we got to know that as per Mexican Government order published in October 26th, 2022, that Daylight Saving Time (DST) in the United Mexican States will not be observed in 2023 calendar year.

Second is to compare the UAT and Production environment.

• No change in code at application end.
• There is no difference in OS time zone settings in application and database server.
• The result of Mexico Time Zone in sys.time_zone_info were different.

Query to check the Mexico Time Zone information:

SELECT * FROM sys.time_zone_info
WHERE name LIKE '%Mexico%'

Result:

The above result was from UAT where the output is showing correct. Likewise we checked it in Production environment and result is as follows:

Certainly we could see the difference as mentioned by the customer. What next?

• Checked the SQL Server patch and both UAT and Production Instance were in the same patch level
• Compared the OS patch level on both the servers and we could find the difference in patches installed

Being DBA you need to know how to verify these stuffs to be able to quickly identify the issue. Below screen shot will help you with the path:

The highlighted patch was not installed in Prod because it was scheduled for next month patching cycle.

A quick read about the patch in the KB article  KB5023702 clarifies the issue:

Resolution & Conclusion:

The resolution is a no brainer once you find the issue. You have to follow the process as per your organization and get the patch installed in the server.

To conclude incorrect time zone in sys.time_zone_info is not a SQL Server issue. However being DBA you should be able to troubleshoot, find the root cause and suggest the solution. If customer complains about one server shows correct time zone while the other is incorrect without doing any changes in the application end, you should perform a thorough check at server end. The issue occurred because of not having latest OS patch and you can resolve it by going through the relevant article of the OS patch and get it installed.

Please spread the word:

https://sqlgeekspro.com/incorrect-mexico-time-sql-server/

Today we are going to discuss about how to resolve the “Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped”

Introduction

If you are a SQL Server DBA, you may encounter the Error 15141 when trying to delete a login id. First of all let me show you how the error message looks like in SSMS when you try to delete/drop a login.

In the above example screen shot we are trying to delete the login id ‘Admin’. I have observed that some DBA(s) do not read the error message carefully and starts beating about the bush.

There are very similar error messages while dropping logins as you can refer the following links:

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

Error 15141: The Server Principal Owns One or More Endpoints

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

To emphasize if you observe the above error message clearly reads that the principal (or login) owns one or more server roles, which prevents you from dropping it.

Cause Of the Error 15141: The Server Principal Owns One or More Server Roles

When a server principal or login owns a server role, you cannot drop the login unless you first transfer ownership of the server role to another login. As a matter of fact SQL Server never allows you to drop a login if it owns any object. Hence it throws the error preventing you to drop the server principal or login.

Resolution

To resolve the error, you first need to identify the Server Roles owned by the login or server principal. Then you need to transfer the ownership for each of the server roles to ‘sa’ or any other login as per your organization standard.

1. Query to Identify the Server Roles the Login owns

SELECT sp1.name AS ServerRoleName, 
       sp2.name AS RoleOwnerName
       FROM sys.server_principals AS sp1
       JOIN sys.server_principals As sp2
       ON sp1.owning_principal_id=sp2.principal_id
       WHERE sp2.name='Admin' --Change the login name

Sample Output:

Here in the above example it shows that the login id ‘Admin’ owns two Server roles. On the contrary if the login would have own one or more database role(s), it would allow to delete the login but not the user. Now we’ll change the ownership.

2. Query to Change the Server Role Owner:

USE [master]
GO
ALTER AUTHORIZATION ON SERVER ROLE :: [ServerRole-Test] TO [sa] --Change The ServerRole Name and login Name
GO
ALTER AUTHORIZATION ON SERVER ROLE :: [AnotherServerRole-Test] TO [sa] --Change The ServerRole Name and login Name
GO

3. Drop the Login:

USE [master]
GO
DROP Login [Admin] --change the login Name

Query Result:

As can be seen now the the drop command completed successfully.

Conclusion:

To summarize the error Error 15141: The Server Principal Owns One or More Server Roles and cannot be dropped occurs when the login or server principal owns one or more Server Roles and you are trying to drop the login. Obviously you should not take any knee jerk reaction. After all you are trying to resolve the issue. Hence you should first list out the Server Roles which the login owns. Then change the ownership to [sa] or any other login as per your organization standards. Finally delete or drop the login. Definitely this should resolve the issue. Important to realize that this method will allow you to delete the login even if the login owns and database roles. Hence to repeat you need to be very careful while working in production systems.

Please spread the word:

https://sqlgeekspro.com/server-principal-owns-one-or-more-server-roles/

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

Please spread the word:

https://sqlgeekspro.com/error-15141-the-server-principal-owns-one-or-more-availability-groups/

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

Please spread the word:

https://sqlgeekspro.com/error-15434-could-not-drop-login-user-is-currently-logged-in/