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
Hai Pinal,
Since the process is done on the master database we cannot see the red arrow, if the same is done on any other database i think we could see the red arrow.
Thanks.
Puzzle 2: Sql logins and database users are “loosely coupled”. Logins are used for authentication and access to the SQL server, Database users are used for authorization or assigning permissions to database objects. Database users are mapped to logins using a SID, but just because the login is disabled does not mean the database user is disabled. You can actually have a database user without a login (orphaned user), you can change the login the user is mapped to with an Alter User statement.
Puzzle 1. Hidden tiger is in the stripes of the tiger.
“THE HIDDEN TIGER” is written on the tiger’s body
As far my knowledge is concern User belongs database and Login belongs to SQL Server. we can create any user in the database and we can link the user with a specific login.
Hi Pinal Sir “The Hidden Tiger” in a image created by american wildlife artist Rusty Rust which is “CLEARLY WRITTEN ON THE STRIPES OF AN STANDING TIGER WHICH WAS LOOKING AT ME!!” ;)
The red arrow indicates that the user don’t have connect permission to the data base. By default, Guest, Information_Schema and sys accounts don’t have connect permission to the concern DBs.
Seems if we create a new user the connect permission is granted by default.
Because of you have disabled login access but not disable the database access level. Red arrow not showing in database–> security–>user, But red arrow shown in server–>security–>user because you have disable the login level. Is it correct Boss(Pinal)…
Puzzle 1: I Got it. see the tiger body carefully, and you will find “THE HIDDEN TIGER” on his body. It’s starting from neck to foot.
Puzzle 1 : Its written on d body of that tiger “the hidden tiger” :D
Tiger is behind the first left tree
Puzzle 1 ans : The hidden Tiger is Written on the tigers body……..
Puzzle 2 ans: Hi sir, i read u r post & do follow all steps u r mention above & in my case it shows Red-Arrow pointing downwards. as this is a puzzle question so i guess u have taken this screen shot before refreshing the SSMS window .
Bcoze after refreshing it display arrow.
Puzzle 1 : The hidden Tiger is Written on the tigers body……..
Puzzle 2 ans :
Because of you have disabled login access but not disable the database access level. Red arrow not showing in
database–> security–>user,
But red arrow shown in
server–>security–>user
because you have disable the login level access.
Puzzle 2 ans :
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
Puzzle 2 ans :
Login are require for authorization. User are created with login.
When we diable the login, its show red mark on it. Due to this nobody can get login in sql server or connection string.
But different databases can access objects because user is not disable.
Dear Dave,
It worked for me. i mean i haveexecuted all your commnad as its & showing red down arrow mark.did i do anything wrong?
Regards,
Sanjeev Kumar
I found the hidden tiger. Nice one…
Hello Sir,
I am staying in Flat and we have a common parking. everyone has key for parking gate, whom so ever like late long driving can open parking gate, move their bike/car out and close it. :) Do you think if some one change the lock of parking gate, I will not be able to open my car/bike?
I think in SQL Server, User and Login both are different entities. Login is more kind of Authorization (on server front [similar like key of parking gate]) where as Users are more like Authentication (on server front [similar to key of your bike/car]). We can link different Users to same Login for accessing different database.
Disabling a Login will stop people to connect to database but User will not affect from it.
Happy New Year & Cheers!!!