I have received lots of good responses to the puzzles, quizzes and brain teasers posted on this blog post. As the year is ending, I have decided to give two interesting puzzles for you. The first one is easy and the second one is equally uncomplicated, but let us see if any of you can come up with a logical answer to it.
Puzzle 1: Find “The Hidden Tiger”
Find “The Hidden Tiger” in the following image created by American wildlife artist Rusty Rust. Just to give you a hint – there is already one tiger standing and looking at us. Now you have to find “The Hidden Tiger”. Trust me; it is very much in front of your eyes. You just have to find it. Trust me it is there.
Puzzle 2: Why no Red-Arrow on User when Login is disabled
Earlier I posted SQL SERVER – Merry Christmas and Happy Holidays – Database Properties – Number of Users and asked a question why there is Red-Arrow on the username and I have received lots of replies which say the reason behind it was that the login is disabled and the user does not have an access to the database.
Let us now create scenario. We will do following three tasks:
- Create Login
- Create User and associate with the Login
- Disable Login
USE [master]
GO
-- Create Login
CREATE LOGIN [sqlauthority] WITH PASSWORD=N'SQL@Authority1'
GO
-- Create User
USE [AdventureWorks]
GO
CREATE USER [sqlauthority] FOR LOGIN [sqlauthority] WITH DEFAULT_SCHEMA=[dbo]
GO
-- Disable Login
USE [master]
GO
ALTER LOGIN [sqlauthority] DISABLE
GO
Now we will go and refresh SSMS indows and check the user. The user will not have the Red-Arrow. Now, try to connect to SQL Server using the same user and it will return the following error, suggesting that the login failed as the account is disabled.
Login failed for user ‘sqlauthority’. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)
Now here is the question for you:
If Login is disabled, why does the user associated with the same have no Red-Arrow pointing downwards?
I am sure that finding the answer to this will be a great learning experience for all.
Reference: Pinal Dave (https://blog.sqlauthority.com)
34 Comments. Leave new
Can anyone please help for the below code…
I’m trying to move a file from C:\ to D:\
but I’m getting an output as “Access is denied.”
I’ve configured “xp_cmdshel” value to 1
BEGIN
declare @FileName varchar(150)
declare @sql varchar(500)
set @FileName = ‘Test.txt’
set @sql = ‘MOVE C:\’ + @FileName + ‘ D:\’
EXEC master..xp_cmdshell @sql
End
Got the solution..
Changed the access to “Full Control” of the drive…
and it’s working perfectly….
Thanks…
the red arrow mark means the login for corresponding user does not exist
Hi Pinal,
Puzzl1 Reply:- keyword “THE HIDDEN TIGER” is written on the Tiger’s body near it’s neck.
Puzzl2:- The user log in is disbled but still there is an schema (called dbo)associated with that user. Once we go that schema and ‘View the database permissions we can find that the Connect permission is granted. If we go and uncheck that Grant- connect ,it will show the downwards Red-Arrow.
Regards,
Chandrashekhar
MSBI Consultant
Puzzle 1 : The hidden Tiger is Written on the body… please see carefully it’s written on stripes ” THE HIDDEN TIGER”…
Puzzle 1: The Hidden tiger is on the stripes of the tigers body.
Puzzle 1: Clearly written on the stripes of the standing tiger looking at us.
Puzzle 2:
Because, you have disabled login level access but not disable the database level access.
Red arrow not showing in
database–> security–>user,
But red arrow shown in
server–>security–>user
If we disable the login then user associated with that particular login will be automatically get disabled, so you can login into the server until you enable the login. You can see the RED MARK in login.
Logins are server level & users or only database level.
Typo mistake , you cant login to the server until you enable the login
Pinal
Nice simple puzzle for No.2 answer is that it is a “SQL User without login” what is also known as “loginless” user.. so you can’t authenticate with i but can revert to using it as for its security rights
Puzzle 1 :
2nd “TIGER”.. is written on the body of the tiger.
Puzzle 2 :
As we’ve created a “user” and associated it with “login”. Now after disabling the “login”, “user” is still present there but that particular “login” is not authorized for this “user” (as we’ve disabled the “login”).
Puzzle1: “THE HIDDEN TIGER” is written on the body of the tiger
Puzzle2: Here we have disabled the login not the user, I think that is the reason we are not seeing that red down arrow mark on the user.
The Hidden tiger is in White..Ha :-)