locked
Mapping database to login RRS feed

  • Question

  • hi all

       i am new Security concept, please help me how to map database based on login in sql server.i am having 20 database in window server, i want to map particular database to specific user.one small doubt, what is the difference between login and user.

     any helps would be appreciated.

    Thanks

    Saturday, December 1, 2012 1:48 AM

Answers

  • A login allows a person or process to connect to SQL Server.  A database user, which is mapped to a login (same as the user name by default), allows the login to use one or more databases.  You can also specify a default database for a login so that a default database context is set (if not otherwise specified during connection).

    --Windows login and user
     USE MyDatabase;
     CREATE LOGIN [MyDomain\SomeLogin] FROM WINDOWS
     WITH DEFAULT_DATABASE = MyDatabase;
     CREATE USER [MyDomain\SomeLogin];
    
     --SQL login and user
     CREATE LOGIN [SomeSqlLogin] WITH PASSWORD='Mic0opmexp@Ssword',
     DEFAULT_DATABASE = MyDatabase;
     CREATE USER [SomeSqlLogin];
     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Rama Udaya Saturday, December 1, 2012 3:58 AM
    • Marked as answer by Maggie Luo Monday, December 10, 2012 4:57 PM
    Saturday, December 1, 2012 2:43 AM

All replies

  • A login allows a person or process to connect to SQL Server.  A database user, which is mapped to a login (same as the user name by default), allows the login to use one or more databases.  You can also specify a default database for a login so that a default database context is set (if not otherwise specified during connection).

    --Windows login and user
     USE MyDatabase;
     CREATE LOGIN [MyDomain\SomeLogin] FROM WINDOWS
     WITH DEFAULT_DATABASE = MyDatabase;
     CREATE USER [MyDomain\SomeLogin];
    
     --SQL login and user
     CREATE LOGIN [SomeSqlLogin] WITH PASSWORD='Mic0opmexp@Ssword',
     DEFAULT_DATABASE = MyDatabase;
     CREATE USER [SomeSqlLogin];
     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Rama Udaya Saturday, December 1, 2012 3:58 AM
    • Marked as answer by Maggie Luo Monday, December 10, 2012 4:57 PM
    Saturday, December 1, 2012 2:43 AM
  • You might be interested in this information I have placed on the MSDN SQL Server wiki Database Engine Permission Basics

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, December 3, 2012 6:01 PM