locked
what is the difference between Creating LOGIN and USERS in SQL sERver 2005? RRS feed

  • Question

  •  

    hi all,

    Guys i am new to SQL server.As you know we can create on Login and associate many USERS to that.

    As you know when you create LOGIN ,u must choose to which Database is this Login Related?

    Now u know that actually when you create a LOGIN a User with the same name will create.

    and this user is actually the one for maping to the regarding database,

    so what is the use of creating more than one User?I cant understand that pleze help/

    Wednesday, August 6, 2008 3:52 AM

Answers

  •  simosi wrote:

    thanks Madhu,

    I read http://vyaskn.tripod.com/sql_server_security_best_practices.htm . it was really cool,

    but it doesnt explained that how can we  implement one LOGIN and Many users and then make use of them.

    You know my doubt and problem is that if we create many users for one login then after we logged in by our login into SQL SERVER Instance then which user is activated from the defined users for this particular LOGIN>?

     

    From the above mentioned site

     

    User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.

     

    So the point is , you can not create multiple users in a database which mapped to same login .

     

    There are functions which will tell you which login /USER etc

     

    SYSTEM_USER()

    CURRENT_USER()

    User_Name()

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    • Proposed as answer by varunragul Monday, March 29, 2010 6:03 AM
    • Marked as answer by Kalman Toth Wednesday, July 27, 2011 4:47 PM
    Wednesday, August 6, 2008 5:40 AM

All replies

  • I think you have slightly confused. One login can map to many users, but in deferent databases. One login can map to only one user in a particular database.

     

    Check this article on SQL Server Security model

    http://vyaskn.tripod.com/sql_server_security_best_practices.htm

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    Wednesday, August 6, 2008 4:37 AM
  • Login : Is an user which can get access to the database. It can be a sql server user like ( sa ) or can be an active directory user / group, like DOMAIN\SCOTT.

    User: It is an user which is related to a login and it has access to a database. Each login must have a default database because when the account will connect it needs that default database. You can use one login to connect to different databases with different users and having different rights.

    There's another option called schema, it allows us to define an owner (schema) for our database objects, for example, you can create a schema called DEMO, next you can create a table called DEMO.Employee. personally, I like this option Stick out tongue

    I hope it can help you.

    Regards,

    Roy.

    Wednesday, August 6, 2008 4:42 AM
  • thanks Madhu,

    I read http://vyaskn.tripod.com/sql_server_security_best_practices.htm . it was really cool,

    but it doesnt explained that how can we  implement one LOGIN and Many users and then make use of them.

    You know my doubt and problem is that if we create many users for one login then after we logged in by our login into SQL SERVER Instance then which user is activated from the defined users for this particular LOGIN>?

    Wednesday, August 6, 2008 5:34 AM
  •  simosi wrote:

    thanks Madhu,

    I read http://vyaskn.tripod.com/sql_server_security_best_practices.htm . it was really cool,

    but it doesnt explained that how can we  implement one LOGIN and Many users and then make use of them.

    You know my doubt and problem is that if we create many users for one login then after we logged in by our login into SQL SERVER Instance then which user is activated from the defined users for this particular LOGIN>?

     

    From the above mentioned site

     

    User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.

     

    So the point is , you can not create multiple users in a database which mapped to same login .

     

    There are functions which will tell you which login /USER etc

     

    SYSTEM_USER()

    CURRENT_USER()

    User_Name()

     

    Madhu

    SQL Server Blog

    SQL Server 2008 Blog

     

    • Proposed as answer by varunragul Monday, March 29, 2010 6:03 AM
    • Marked as answer by Kalman Toth Wednesday, July 27, 2011 4:47 PM
    Wednesday, August 6, 2008 5:40 AM
  • Actaually befoe continuing i must let u know the diff betweeen Login & users... loging gives u to permission to enter in the room/SQl server. & user gives u to do activities with Objects in the Server or u can say the items in the room.. if u dont hav access on the items u cant touch them as in the case of SERVEr if u dont hav permissions u cant do any activites with the server objects. so when u crete login just crete from security TAB. So once the login is created then by clicking in the Databases & then add that login into that... 

     

    Friday, October 10, 2008 6:04 PM
  • Actaually befoe continuing i must let u know the diff betweeen Login & users... loging gives u to permission to enter in the room/SQl server. & user gives u to do activities with Objects in the Server or u can say the items in the room.. if u dont hav access on the items u cant touch them as in the case of SERVEr if u dont hav permissions u cant do any activites with the server objects. so when u crete login just crete from security TAB. So once the login is created then by clicking in the Databases & then add that login into that... 

     


    if I have a login associated with different users ie login is "Akbar" and user1 is "Akbarread" with read only permission and 2nd user2 is "Akbarwrite" with read and write permision on the same database now how can i connect with specific user? which is when ever required.
    Sunday, March 28, 2010 9:28 PM
  • if I have a login associated with different users ie login is "Akbar" and user1 is "Akbarread" with read only permission and 2nd user2 is "Akbarwrite" with read and write permision on the same database now how can i connect with specific user? which is when ever required.?????????
    Sunday, March 28, 2010 9:30 PM
  • You cannot have same login associated with more than one users in a database. 1 Login can only be mapped with 1 user.
    Wednesday, May 26, 2010 7:13 PM

  • Logins are a database server level [instance level] objects. login provides access to the server and to further get access to a database, a user mapped to the login must exist in the database. You can get the login details from sys.server_principals (SELECT * FROM sys.server_principals). Server level permissions will be given to logins.

    Users are a database specific objects.Users are mapped to logins and the mapping is expressed by the SID property of logins and users. Different users details for a specific database will be shown in sys.database_principals (SELECT * FROM sys.database_principals).


    Swami A Polnati.
    • Proposed as answer by steve42071 Monday, December 17, 2012 10:32 PM
    • Unproposed as answer by steve42071 Monday, December 17, 2012 10:57 PM
    • Proposed as answer by steve42071 Monday, December 17, 2012 10:57 PM
    Wednesday, July 20, 2011 10:52 AM