Alter User

  • Andre Ranieri

    SSCrazy

    Points: 2819

    +1, I'm rather curious about that restriction and didn't find anything enlightening in BOL, which states:

    The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause. Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a SQL Server login.

    The name of the user will be automatically renamed to the login name if the following conditions are true.

    Does that mean that, should we end up with a user that is not mapped to any logins, the only recourse is to drop that user and recreate it as mapped?

    Andre Ranieri

  • TomThomson

    SSC Guru

    Points: 104772

    Andre Ranieri (2/18/2013)


    +1, I'm rather curious about that restriction and didn't find anything enlightening in BOL, which states:

    The WITH LOGIN clause enables the remapping of a user to a different login. Users without a login, users mapped to a certificate, or users mapped to an asymmetric key cannot be re-mapped with this clause. Only SQL users and Windows users (or groups) can be remapped. The WITH LOGIN clause cannot be used to change the type of user, such as changing a Windows account to a SQL Server login.

    The name of the user will be automatically renamed to the login name if the following conditions are true.

    Does that mean that, should we end up with a user that is not mapped to any logins, the only recourse is to drop that user and recreate it as mapped?

    Andre Ranieri

    Depends on how the user became unmapped. If it was created that way, the only way is to drop and remap. If it was created with an SQL login that has since been dropped, it can be fixed using sp_change_users_login - but that was deprecated in SQL 2008 so maybe SQL 2012 will be the last release to contain it (the same sp can handle the case where the database containing the user is attached to a different instance of SQL Server which doesn't recognise teh SID for the original login).

    Two relevant BoL pages are

    Troubleshoot Orphaned Users (SQL Server) and

    sp_change_users_login (Transact-SQL)

    Since the recommended action instead of using the deprecated sp is to use ALTER USER, which apparently can't fix a user with no login, the future is not bright - unless what the statement about ALTER USER really means is not that it can't ever fix up a user without a login but just that it can't fix up a user which has never had a login but can fix up a user who has had a login but is now orphaned (which would save recreating role memberships and individual permissions, which otherwise would be needed, which would be an out and out pain).

    Tom

  • @Cassie

    SSC Eights!

    Points: 887

    +1

    Great question.

    I needed some search and reading on the internet.

  • john.arnott

    SSChampion

    Points: 11882

    sestell1 (2/18/2013)


    Wow, interesting... and very odd.

    I'd be curious to know the reason behind this restriction.

    +1 I'm hoping we hear an authoritative answer to this, but would guess that it's got to do with how a user without login would be used to begin with, being given impersonation access or guest access. It's just a different animal than one with its own security identifier. [caveat: I'm a SQL developer, not a DBA, so yes, this is just speculation.]

  • Anipaul

    SSC-Insane

    Points: 24681

    Excellent question..nice to learn something new....

  • okbangas

    SSChampion

    Points: 11773

    john.arnott (2/18/2013)


    sestell1 (2/18/2013)


    Wow, interesting... and very odd.

    I'd be curious to know the reason behind this restriction.

    +1 I'm hoping we hear an authoritative answer to this, but would guess that it's got to do with how a user without login would be used to begin with, being given impersonation access or guest access. It's just a different animal than one with its own security identifier. [caveat: I'm a SQL developer, not a DBA, so yes, this is just speculation.]

    A user without a login would be very handy in TFS or other version control systems, that is if you were able to link them to a login after deployment ...



    Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

    Concatenating Row Values in Transact-SQL[/url]

  • (Bob Brown)

    SSCrazy

    Points: 2705

    Thanks for the question.

  • SQLRNNR

    SSC Guru

    Points: 281243

    L' Eomot Inversé (2/16/2013)


    Nice straightforward question.

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    L' Eomot Inversé (2/16/2013)


    But I wonder why this strange restriction that alter user can't provide a login for a user who doesn't already have one exists. Presumably MS has some reason for it, but BoL just tells us about the restriction and doesn't give any reason for it (tha's fairly standard for BoL, of course).

    My guess is that ALTER USER WITHOUT LOGIN was explicitly added to the product to introduce a way to create a principal and then give authorizations to that principal with 100% certainty that nobody would ever be able to log in to that account and start abusing the authorizations. Allowing ALTER USER to add a login would open up a backdoor.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • TomThomson

    SSC Guru

    Points: 104772

    Hugo Kornelis (2/28/2013)


    My guess is that ALTER USER WITHOUT LOGIN was explicitly added to the product to introduce a way to create a principal and then give authorizations to that principal with 100% certainty that nobody would ever be able to log in to that account and start abusing the authorizations. Allowing ALTER USER to add a login would open up a backdoor.

    I'm not sure whether that makes sense or not. Allowing the user to be impersonated wouldn't be the same as allowing the login to be impersonated even if there were an associated login. I suppose it's an extra barrier to attack, which is a good thing even if there's another barrier blocking the same attack, so it's a good thing, but not very interesting. On the other hand, if the idea is to allow impersonation of the user only through signed modules, not having a login would be rather useful.

    Tom

Viewing 10 posts - 16 through 25 (of 25 total)

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