locked
Security newbie question RRS feed

  • Question

  • Hi

    There are two security areas one under SQL Server and the other under specific database. If I need to allow a user access to a database where do I need to create the security login under, under SQL Server, under specific database or under both?

    Thanks

    Regards

    Tuesday, August 21, 2012 3:59 AM

Answers

  • Hello,

    In both.

    First you have to add the login to SQL Server security to grant login permission; but you don't need to add the new login to any further server role membership.

    Then you have to add the new login to the database security as "database user" to grant access to that database. Additional you have to grant permissions for executing stored procedure and/or selecting specified tables and so on. Optional you can add the user to a predefined database role like "db_datareader" to allow him to select data from all tables/views.


    Olaf Helper
    Blog Xing

    • Proposed as answer by Maggie Luo Wednesday, August 22, 2012 9:02 AM
    • Marked as answer by Maggie Luo Tuesday, August 28, 2012 6:36 PM
    Tuesday, August 21, 2012 4:27 AM

All replies

  • Hello,

    In both.

    First you have to add the login to SQL Server security to grant login permission; but you don't need to add the new login to any further server role membership.

    Then you have to add the new login to the database security as "database user" to grant access to that database. Additional you have to grant permissions for executing stored procedure and/or selecting specified tables and so on. Optional you can add the user to a predefined database role like "db_datareader" to allow him to select data from all tables/views.


    Olaf Helper
    Blog Xing

    • Proposed as answer by Maggie Luo Wednesday, August 22, 2012 9:02 AM
    • Marked as answer by Maggie Luo Tuesday, August 28, 2012 6:36 PM
    Tuesday, August 21, 2012 4:27 AM
  • You can also create a user without login  as well :-)

    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Tuesday, August 21, 2012 6:56 AM
  • If you are running SQL 2012 you can use Partially Contained Database  see the example E in the CREATE USER BOL page. :)

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Tuesday, August 21, 2012 7:02 AM