locked
User without login purpose RRS feed

  • Question

  • HI, we have a database that has a user created without login. I was wondering why using a user without login? Is there a reason for this? Can we use this user and how since it is not mapped to any login?

    Thank you,
    Ccote

    Thursday, November 16, 2006 11:22 AM

Answers

  • Users explicitly created with login are useful when you want to impersonate a context that is restricted to a database. Unlike orphaned users, which can be remapped to a login, loginless users cannot be mapped to any login. You can use them, for example, as a replacement for application roles. Rather than creating and setting an application role, you could do:

    CREATE USER db_restricted WITHOUT LOGIN

    EXECUTE AS USER = 'db_restricted' WITHOUT REVERT

    Thanks
    Laurentiu

    Thursday, November 16, 2006 6:53 PM
  •   I posted an article on my blog that hopefully will help to answer a few questions on this feature: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

     

      Let us know if you have further questions or comments,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, November 20, 2006 7:53 PM

All replies

  • This is an orphaned user. You get these user if you restore a database to a server without creating / mapping a user on server level to the database users. These users are not usable unless you map a server login to the user.


    HTH, Jens K. Suessmeyer.

    ---
    http://www.sqlserver2005.de
    ---
    Thursday, November 16, 2006 4:27 PM
  • HI, thank you. But Sql server allows you to create orphan users? I mean in the bol, when I searched for CREATE USER, I found that we can create users using the WHITOUT LOGIN clause. I was just wondering what that clause is used for.

    Thank you,
    Ccote

    Thursday, November 16, 2006 5:34 PM
  • Hi, orphaned users exists in SQL Server 2000/2005 because you restore the database to a server different of a original.

    Orphaned users exists because sys.database_principals (SQL2k5) or sysusers (SQL2k) are not clear when you backup them.

    You can delete this users and create another.

    SQL2k5: DROP USER or DROP LOGIN

    SQL2k: sp_revokedbaccess

     

    Regards,

    Thursday, November 16, 2006 6:12 PM
  • Users explicitly created with login are useful when you want to impersonate a context that is restricted to a database. Unlike orphaned users, which can be remapped to a login, loginless users cannot be mapped to any login. You can use them, for example, as a replacement for application roles. Rather than creating and setting an application role, you could do:

    CREATE USER db_restricted WITHOUT LOGIN

    EXECUTE AS USER = 'db_restricted' WITHOUT REVERT

    Thanks
    Laurentiu

    Thursday, November 16, 2006 6:53 PM
  • Errata: In my previous message, "Users explicitly created with login" should have been "Users explicitly created without login".
    Friday, November 17, 2006 9:58 PM
  •   I posted an article on my blog that hopefully will help to answer a few questions on this feature: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

     

      Let us know if you have further questions or comments,

    -Raul Garcia

      SDE/T

      SQL Server Engine

    Monday, November 20, 2006 7:53 PM