Post reply

Difference between Security node under the server and the security node under a database

  • May 5, 2011 at 4:46 am

    #237663

    I sort of know the answer on this but want to see what you guys say.

    In relation to users what is the difference between adding users in

    Servername>Security>Logins and Servername>Databases>Databasename>Security>Users in the object explorer window of sql server 2005.

    as much info would be very appreciated.

    thanks

  • May 5, 2011 at 4:57 am

    #1320300

    Logins allow connections to the server. Users allow access to the specific databases. A user with no login cannot ever connect to the server (though can be used for impersonation)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 20, 2011 at 4:20 pm

    #1328026

    In relation to users what is the difference between adding users in

    Servername>Security>Logins and Servername>Databases>Databasename>Security>Users in the object explorer window of sql server 2005.

    To clarify some terminology there are no "Users" defined in "Servername>Security>Logins". A Login (server scope) is different from a User (database scope).

    A Login can be mapped to Users in none or any number of databases, but in any given database a login can only be mapped to a maximum of one user.

    A Database User is mapped to at most one server login. It is a special case but it is also possible to create a User in a database that is not mapped to any Login.

    The line is blurry, and it is about to become more blurry with the addition of Contained Databases in SQL11, but I am comfortable making this generic distinction in SQL 2008:

    -> Server Logins are associated with Authentication to the server, i.e. controlling access to the server. This includes password management for SQL Server Logins, the delegation to Active Directory (or Local Machine) for the password management of Windows Logins and authentication via Certificates.

    -> Database Users are associated with Authorization in a database, i.e. controlling access to objects within a specific database.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 23, 2011 at 2:21 am

    #1328333

    ps_vbdev (5/5/2011)

    In relation to users what is the difference between adding users in

    When you see the list of Logins and Users in the UI, they look similar and may confuse you.

    I suggest, you practice CREATE/ALTER/DROP LOGIN/USER using command to become familiar with the relation and difference between Login and User.

  • June 2, 2011 at 12:41 pm

    #1333303

    Take a look at this article: http://vyaskn.tripod.com/sql_server_security_best_practices.htm

    Best Regards,

    Datta

    Dattatrey Sindol
    Blog: Datta's Ramblings on Business Intelligence 'N' Life[/url]

    This information is provided "AS IS" with no warranties, and confers no rights.

Viewing 5 posts - 1 through 4 (of 4 total)

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