SQL SERVER – Year End Brain Teaser – Disabled Login and Associated User Without Disabled User Red Arrow

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 (http://blog.sqlauthority.com)

About these ads

35 thoughts on “SQL SERVER – Year End Brain Teaser – Disabled Login and Associated User Without Disabled User Red Arrow

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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)…

  6. 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.

  7. 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.

  8. 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

  9. 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.

  10. 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

  11. 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!!!

  12. 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

  13. 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

  14. 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

  15. 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.

  16. 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

  17. 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”).

  18. 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.

  19. Pingback: SQL SERVER – Weekly Series – Memory Lane – #009 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s