none
Permission error with ALTER USER

    Question

  • I have granted a user both "ALTER ANY LOGIN", and "ALTER ANY USER" permissions. They are able to create logins and rename users by running e.g. "ALTER USER u3 WITH name=u4", but get a permission error when trying to re-map a user to a login (even if the re-mapping is to the already mapped login)

    ALTER USER [u4] WITH login=[u5]

    Msg 15151, Level 16, State 1, Line 2
    Cannot alter the user 'u5', because it does not exist or you do not have permission.

    Running this command as administrator works fine.

    Any help would be much appreciated!

    Saturday, February 15, 2014 12:35 PM

Answers

  • Admittedly, I culled that text from BOL 2008 (which I prefer, because of the help viewer). However, before I went there, I had tried this repro, which conforms to what BOL 2008 says, both on SQL 2008 and SQL 2012:

    USE master
    go
    CREATE LOGIN privvy WITH PASSWORD = 'Junkie'
    GRANT ALTER ANY LOGIN TO privvy
    CREATE LOGIN otherlogin WITH PASSWORD = 'Halmstad'
    CREATE LOGIN otherlogin2 WITH PASSWORD = 'Falkenberg'
    go
    USE tempdb
    go
    CREATE USER privvy
    CREATE USER otherlogin
    GRANT ALTER ANY USER TO privvy
    --GRANT CONTROL TO privvy
    go
    EXECUTE AS LOGIN = 'privvy'
    go
    ALTER USER otherlogin WITH LOGIN = otherlogin2
    go
    REVERT
    go
    DROP USER privvy
    DROP USER otherlogin
    go
    DROP LOGIN privvy
    DROP LOGIN otherlogin
    DROP LOGIN otherlogin2

    The ALTER USER command fails unless you uncomment the GRANT CONTROL command.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eugene1234567 Sunday, February 16, 2014 2:55 AM
    Saturday, February 15, 2014 7:03 PM

All replies

  • Could it be, that the user that you are trying to re-map, has CONTROL permission on the database?

    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, February 15, 2014 3:39 PM
  • Books Online, the topic on ALTER USER, the Permission sections says (emphasis mine):

    /To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema.

    Requires CONTROL permission on the database to remap a user to a login./


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 5:02 PM
  • Books Online, the topic on ALTER USER, the Permission sections says (emphasis mine):

    /To change the name of a user requires ALTER ANY USER on the database. To change the default schema requires ALTER permission on the user. Users can change only their own default schema.

    Requires CONTROL permission on the database to remap a user to a login./


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    well, at least in my version of BOL that phrase is a little more specific:

    To change the user name, target login, or SID of a user having CONTROLpermission on the database requires the CONTROL permission on the database.

    So that's why I asked above... honestly I haven't tested it, but it makes highly sense to me.

    http://technet.microsoft.com/en-us/library/ms176060.aspx


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, February 15, 2014 5:17 PM
  • Admittedly, I culled that text from BOL 2008 (which I prefer, because of the help viewer). However, before I went there, I had tried this repro, which conforms to what BOL 2008 says, both on SQL 2008 and SQL 2012:

    USE master
    go
    CREATE LOGIN privvy WITH PASSWORD = 'Junkie'
    GRANT ALTER ANY LOGIN TO privvy
    CREATE LOGIN otherlogin WITH PASSWORD = 'Halmstad'
    CREATE LOGIN otherlogin2 WITH PASSWORD = 'Falkenberg'
    go
    USE tempdb
    go
    CREATE USER privvy
    CREATE USER otherlogin
    GRANT ALTER ANY USER TO privvy
    --GRANT CONTROL TO privvy
    go
    EXECUTE AS LOGIN = 'privvy'
    go
    ALTER USER otherlogin WITH LOGIN = otherlogin2
    go
    REVERT
    go
    DROP USER privvy
    DROP USER otherlogin
    go
    DROP LOGIN privvy
    DROP LOGIN otherlogin
    DROP LOGIN otherlogin2

    The ALTER USER command fails unless you uncomment the GRANT CONTROL command.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Eugene1234567 Sunday, February 16, 2014 2:55 AM
    Saturday, February 15, 2014 7:03 PM
  • Thanks for the repro Erland. I can confirm your result on SQL 2012 SP1

    So BOL has for some reason been updated with wrong information. Maybe in some RTM it was supposed to be different, until someone realized that at second sight it is still dangerous. Who knows.

    So yes: CONTROL permission is in fact needed for remapping a user.


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, February 15, 2014 7:24 PM
  • Connect item for the doc bug filed:
    https://connect.microsoft.com/SQLServer/feedback/details/817554/incorrect-text-about-permissions-in-the-topic-for-alter-user


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, February 15, 2014 8:33 PM
  • okay, voted

    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    Saturday, February 15, 2014 9:00 PM
  • Yep, giving CONTROL works, thanks for that. However, I'm not really happy about having to give such a powerful permission to a helpdesk user... I suppose what's necessary is having re-mappings be done by a stored procedure that runs under 'sa' and makes sure the re-mapping is ok security-wise. Do you have any other ideas?
    Sunday, February 16, 2014 8:30 AM
  • Yes, for many of these things it is a good idea to put the command in a stored procedure which you sign with a certificate, create a user (and login if needed) from the certificate, grant that user the permissions needed. Of course, this requires that you know in advance which commands you need to package this way.

    For a longer disucssion about this technique, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, February 16, 2014 10:51 AM
  • Okay thanks, I've read parts of your write-up before, it's very good.
    Sunday, February 16, 2014 11:45 AM