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.

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

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:

Solarwinds
  • 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)

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

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?


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

I am sure that finding the answer to this will be a great learning experience for all.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – Effect of Compressed Backup Setting at Server Level on Database Backup
Next Post
SQLAuthority News – A Quick History of Writing Three Books in Year 2011

Related Posts

No results found

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

    Reply
    • Got the solution..

      Changed the access to “Full Control” of the drive…
      and it’s working perfectly….

      Thanks…

      Reply
  • the red arrow mark means the login for corresponding user does not exist

    Reply
  • Chandrashekhar Singh
    December 29, 2011 4:21 pm

    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

    Reply
  • yangamuniprasad
    December 29, 2011 4:47 pm

    Puzzle 1 : The hidden Tiger is Written on the body… please see carefully it’s written on stripes ” THE HIDDEN TIGER”…

    Reply
  • Puzzle 1: The Hidden tiger is on the stripes of the tigers body.

    Reply
  • Debesh P Lohani
    December 30, 2011 5:14 am

    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

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

    Reply
  • Typo mistake , you cant login to the server until you enable the login

    Reply
  • 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

    Reply
  • 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”).

    Reply
  • Prasad Dabbada
    January 19, 2012 9:55 am

    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.

    Reply
  • The Hidden tiger is in White..Ha :-)

    Reply

Leave a Reply

Menu