Post reply

Orphaned users

  • October 30, 2013 at 9:18 pm

    #281870

    Comments posted to this topic are about the item Orphaned users

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • October 31, 2013 at 2:35 am

    #1662942

    Nice question.

    Under drop the login with DBO rights you mention

    DROP LOGIN [Domain\TestSQLDBadmins];

    GO

    I guess you meant

    DROP LOGIN [Domain\TestWindowsDBadmins];

    GO

    ?

  • October 31, 2013 at 3:31 am

    #1662954

    Nice question, it's a pity I got too engaged with the max level of permission and forgot to tick public and db_datareader...

  • October 31, 2013 at 5:25 am

    #1662991

    good question ... thanks

  • October 31, 2013 at 5:28 am

    #1662993

    Good question. Too bad I got caught up in the permissions and forgot to tick that the create worked.

    Thanks,

    ToddR

  • October 31, 2013 at 5:35 am

    #1662998

    Nice question. Missed it though...selected 3 out of the 4 correct. Next time, I guess I'll read it a bit more "slowly." 😀

  • October 31, 2013 at 6:07 am

    #1663013

    I find this one to be a very good question but one that can be easily answered by just analyzing the available options.

    First, you need to select 4 correct answers but there are three (The last ones) that are mutually exclusive. That means that the first three are true.

    Given that, if you know for a fact that the user has permissions to the db_owner role, than you should know that he will have enough privileges to create a table in the database.

    I'm surprised the percentage of correct answers are so low.

    ---------------
    Mel. 😎

  • October 31, 2013 at 6:23 am

    #1663019

    SqlMel (10/31/2013)

    I find this one to be a very good question but one that can be easily answered by just analyzing the available options.

    First, you need to select 4 correct answers but there are three (The last ones) that are mutually exclusive. That means that the first three are true.

    Given that, if you know for a fact that the user has permissions to the db_owner role, than you should know that he will have enough privileges to create a table in the database.

    I'm surprised the percentage of correct answers are so low.

    I just over-thought this one. Maybe others have too. Plus, there was the pressure of it being worth THREE points. 😉

    BTW, I was surprised Steve didn't slip in a humorous Halloween question. I look forward to those holiday questions.

    If you celebrate such things, Happy Halloween! Hope you get lots of >(///)<

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • October 31, 2013 at 6:37 am

    #1663024

    I clearly need some educatin' here. I'm very much not an administrator, so bear with me.

    "TestWindowsUser is in a domain group that is a member of db_owner and another that is a member of db_dataReader."

    I'm lost as to when this happens. I see that he's in two domain groups, but I'm not seeing where those groups are made members of db_owner and db_reader. I see two users from those groups who are assigned those roles... does assigning a user to a role assign every user in that domain group to the same role? That's doesn't seem right.

    Apologies if I'm missing the obvious. I've never worked with this stuff before.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • October 31, 2013 at 6:48 am

    #1663027

    ronmoses (10/31/2013)

    I clearly need some educatin' here. I'm very much not an administrator, so bear with me.

    "TestWindowsUser is in a domain group that is a member of db_owner and another that is a member of db_dataReader."

    I'm lost as to when this happens. I see that he's in two domain groups, but I'm not seeing where those groups are made members of db_owner and db_reader. I see two users from those groups who are assigned those roles... does assigning a user to a role assign every user in that domain group to the same role? That's doesn't seem right.

    Apologies if I'm missing the obvious. I've never worked with this stuff before.

    ron

    The following T-SQL code creates database users which map to the Windows groups. Every member of those groups, when logging into SQL Server, will have the rights of those database users.

    USE DBall

    CREATE USER TestSQLDBadmin FROM Login [Domain\TestWindowsDBadmins];

    CREATE USER TestSQLDBreader FROM Login [Domain\TestWindowsDBreaders];

    GO

  • October 31, 2013 at 6:54 am

    #1663030

    sknox (10/31/2013)

    The following T-SQL code creates database users which map to the Windows groups. Every member of those groups, when logging into SQL Server, will have the rights of those database users.

    USE DBall

    CREATE USER TestSQLDBadmin FROM Login [Domain\TestWindowsDBadmins];

    CREATE USER TestSQLDBreader FROM Login [Domain\TestWindowsDBreaders];

    GO

    Thank you. So if Disney gives Mickey Mouse the ability to fly, every member of The Mickey Mouse Club gains the same ability. Interesting, and entirely confusing. I think I'm not a dba for a reason.

    ron

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • October 31, 2013 at 7:40 am

    #1663049

    Thanks for a very good question based on "orphan user" condition. I had to do lots of R&D and go through various link to gather information. Despite all that efforts I made it wrong on how "create table" statement will work under such situation. Need to check little more on this. 🙂

  • October 31, 2013 at 9:24 am

    #1663123

    ronmoses (10/31/2013)

    sknox (10/31/2013)

    The following T-SQL code creates database users which map to the Windows groups. Every member of those groups, when logging into SQL Server, will have the rights of those database users.

    USE DBall

    CREATE USER TestSQLDBadmin FROM Login [Domain\TestWindowsDBadmins];

    CREATE USER TestSQLDBreader FROM Login [Domain\TestWindowsDBreaders];

    GO

    Thank you. So if Disney gives Mickey Mouse the ability to fly, every member of The Mickey Mouse Club gains the same ability. Interesting, and entirely confusing. I think I'm not a dba for a reason.

    ron

    +1:-D

  • October 31, 2013 at 9:33 am

    #1663125

    jan.dewettinck (10/31/2013)

    Nice question.

    Under drop the login with DBO rights you mention

    DROP LOGIN [Domain\TestSQLDBadmins];

    GO

    I guess you meant

    DROP LOGIN [Domain\TestWindowsDBadmins];

    GO

    ?

    Of course you are right: Never change things in the last minute. I even slept a night over it but yet this error slipped me. Sorry for that. Thank you!

    And I don't remember to have added the (select 4); this hint of course made it easy to solve this question without understanding the problem.

    I was baffled when a customer showed me this effect and let me look like a lemon, even more a grapefruit (bigger, and blushed a bit like I did), but very interesting I think...

    Best regards

    Jens-Peter

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • October 31, 2013 at 9:40 am

    #1663129

    ronmoses (10/31/2013)

    sknox (10/31/2013)

    The following T-SQL code creates database users which map to the Windows groups. Every member of those groups, when logging into SQL Server, will have the rights of those database users.

    USE DBall

    CREATE USER TestSQLDBadmin FROM Login [Domain\TestWindowsDBadmins];

    CREATE USER TestSQLDBreader FROM Login [Domain\TestWindowsDBreaders];

    GO

    Thank you. So if Disney gives Mickey Mouse the ability to fly, every member of The Mickey Mouse Club gains the same ability. Interesting, and entirely confusing. I think I'm not a dba for a reason.

    ron

    Actually it was the group Domain\TestwindowsDBadmins which was given permission to fly (in the form of the database user TestSQLDBadmin) not the individual member of it. Permission for that group to fly was recorded in the database, and was not removed. What happens is that when the sql server login for the group is removed, that login can no longer be used for the group members to login. If however a group member can login to SQL Server for some other reason that member is still able to do whatever that database user, which has not been deleted, can do. This is because their Windows security credentials say that they are part of the group which is mapped to that user. Someone can still log in if an SQL login has been created for their individual windows login, or if they are a member of some other windows group which has an SQL login, as they are here.

    So yes, this is slightly odd (my own first reaction was that it was a bug, but then I realised that it's actuall pretty useful, although not well documented) - but it isn't a case of giving permission to a member and having that extend to the group as you appear to believe, the permission was given to the group in the first place.

    Tom

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply