SQLGeeksPro

Today I am going to show how you can use Windows Authentication With a Different User. Yes, you heard it right, it is possible and it’s one of those Wow factors in SQL Server.
First of all let’s talk about why even someone would need to use the functionality.

Customer may login to their laptop with a different domain account than the account used to connect to SQL Server. This is because of security reasons.

Another situation is customer have different domain account to connect to different SQL Servers. In some organizations this is very common practice.

The below screen shots are of Window 1o and SQL Server 2017.

How to run SSMS as a different user

Create a shortcut of SSMS in taskbar.
Hold Shift Key and right click on the shortcut as shown below:

The next screen will need the credentials for the different user:

Once you click on Ok the login screen of SSMS will appear with the domain user grayed out just like the way we use windows authentication for normal account (the account used to login to the laptop\desktop).

You can open SSMS as a different user without creating a shortcut as well. You need to go to the path of ssms.exe and use the same method.  The file resides in Binn folder of SQL install directory.

Well, you do not have to remember the path. There is an easy way to locate the path as shown below.

Go to Window Search (Windows Key + S) and type SSMS. Right Click on the SSMS icon and choose Open file location.

This will open the SSMS location of Windows Start Menu as shown below. Again right click and open file location.

This is Open the physical file location of ssms.exe as shown below:

Once you reach here you can use the same method as described above to open SSMS using Windows Authentication with a Different User.

Do you have to do this every time you need to use a different windows authenticated user? Let’s see how we can create different shortcuts for different logins.  Continue Reading>>

I got a call in the middle of the night about all the logins in SQL Server has been disabled. Logon failed  due to trigger execution. The situation is quite scary, isn’t it? Well if you know what to do, then it is not except from the fact that you have to login in odd hours.

At first try to connect to the SQL Instance to find out the error:

The error message says “Logon failed for Login ‘sa’ due to trigger execution”. The second and third line of the error is little misleading.

It indicates that there must be a server level trigger someone has created due to which all the logins got disabled. The solution would be, find out the problem trigger and disable it. But you need to find out a way to get in to SQL Instance first.

You guessed it right!  You need to use DAC (Dedicated Admin Connection) to connect in this situation. If you have not read the article where I have explained different ways to enable and connect using DAC click the following link.

Dedicated Administrator Connection (DAC)

Here I am going to show using command prompt. In the video link given at the end of the article, I have demonstrated using SSMS.

Open command prompt and use DAC to connect to the instance. In the below screen shot I am connecting to default instance using windows authentication and then run the below query:

SELECT name, create_date FROM MASTER.sys.server_triggers ORDER BY create_date DESC

As shown in the above example the trigger name is “TestLogin” and if you have observed I have used order by create_date desc, this is because after the latest trigger creation, nobody is able to login.

Now you can disable the trigger using the below query:

disable TRIGGER [TestLogin] ON ALL server

Once the trigger is disabled you will be able to connect to your SQL Instance. You can script out the trigger to check and rectify it.

If you can play sound, go ahead and watch the demo to fix the issue using SSMS:

I hope this helps. I would love to hear from you about any other similar issue or if you have any questions.

You may want to delete that extra Tempdb data file which someone created by mistake or to accommodate a query. Whatever may be the reason, today I am going to show you how to do it and what issues you may face.

You can run the below query to remove the data file:

ALTER DATABASE tempdb
REMOVE FILE LogicalFileName;
GO

Or you can use GUI:

SQL Server throws the following error:

The error message says that it cannot remove the file because it is not empty.

If you just add a file and there is no ongoing activity in the file then it would allow to remove the file this way.

For each file you want to remove, you need to run the following command to empty the file and then run the above query to remove the file:
USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO

But what if it throws the below error:

The error message is about a work table page which can not be removed. Work table is related to cached information, which indicates that you need to clear the cache.

Execute the below queries one by one and after clearing each, try to empty the file again:

DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

The successful run shows as below:

Please keep in mind that whenever you are clearing cache, it can cause performance issues. I am leaving the decision up to you to handle the risk factor according to the environment.

If your environment allows you to restart SQL Service you can just restart which will empty the tempdb files and you can run the remove command:

ALTER DATABASE tempdb
REMOVE FILE LogincalName;
GO

I hope you have already debunked the Myth around Remote Query Timeout option.

You can go ahead and watch this video: