February 18, 2013 at 11:43 am
+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
February 18, 2013 at 12:55 pm
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
February 18, 2013 at 3:07 pm
+1
Great question.
I needed some search and reading on the internet.
February 18, 2013 at 4:11 pm
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.]
February 18, 2013 at 9:19 pm
Excellent question..nice to learn something new....
February 19, 2013 at 2:37 am
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 ...
February 19, 2013 at 3:56 am
Thanks for the question.
February 25, 2013 at 10:24 am
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
February 28, 2013 at 4:29 am
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.
February 28, 2013 at 9:37 am
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 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply