locked
Login vs User permission? RRS feed

  • Question

  • Hi,

    I have an intranet web app that accesses several SQL Server 2012 databases.  I'm using one login identity with all privileges (select, insert, update, delete) to connect to the databases from the web app.  We have several users from different locations that need to perform these operations on the databases. 

    What are the best practices for granting select, insert, update, delete permissions to each authenticated user of the web app?  Do I create each user in each database that they are allowed to connect to?

    Appreciate any help.


    Marilyn Gambone

    Friday, May 27, 2016 4:52 PM

Answers

  • Hi deskcheck1,

    First to be clear in SQL Server: Login is grant for the entry into the SQL SERVER, and a User is grant for login to single Database.

    But you can have one Login to be associated with many users however one per database.

    Each of the above objects can have permissions granted to it at its own level. 

    No you will not have each user for database, you will have one User on SQL Server database layer... for example for web app for school/class management system, every Student will use one User on SQL Server but their authentication and authorisation info will be stored in their table and resolved on Application layer.

    Please take a look at this MSDN documentation: https://msdn.microsoft.com/en-us/library/ht43wsex.aspx

    Read main section and other related sub section and hopeful this will be more clear for you...

    Hope this was helpful for you...

    Best regards!


    (If this was helpful for you, vote for it and propose it as an answer)


    • Edited by Almir VukMVP Friday, May 27, 2016 8:54 PM
    • Marked as answer by deskcheck1 Thursday, June 2, 2016 12:31 PM
    Friday, May 27, 2016 8:51 PM
  • Hi,

    I've tested what I've done (creating users without logins) and it's not an optimum solution.  I re-read what you wrote here and I followed your suggestions.  I added users to a role in each database and then grant the role the permissions needed.

    This works best for me.

    Thanks.


    Marilyn Gambone

    • Marked as answer by deskcheck1 Monday, June 6, 2016 2:26 PM
    Monday, June 6, 2016 2:26 PM

All replies

  • Your post is not entirely clear. You say I'm using one login identity with all privileges. Does this mean that the application is using the same SQL login no matter who connects? In such case, it is only this login that needs to be granted permissions; SQL Server will not know about the users behind the browsers.

    If each user has a login to the server, the simplest would be if you use Windows login and grant access to a Windows group and grant that group the permissions needed in the databases.

    If you are using SQL logins, add the users to a role in each database, and grant that role the permissions needed.

    Friday, May 27, 2016 8:45 PM
  • Hi deskcheck1,

    First to be clear in SQL Server: Login is grant for the entry into the SQL SERVER, and a User is grant for login to single Database.

    But you can have one Login to be associated with many users however one per database.

    Each of the above objects can have permissions granted to it at its own level. 

    No you will not have each user for database, you will have one User on SQL Server database layer... for example for web app for school/class management system, every Student will use one User on SQL Server but their authentication and authorisation info will be stored in their table and resolved on Application layer.

    Please take a look at this MSDN documentation: https://msdn.microsoft.com/en-us/library/ht43wsex.aspx

    Read main section and other related sub section and hopeful this will be more clear for you...

    Hope this was helpful for you...

    Best regards!


    (If this was helpful for you, vote for it and propose it as an answer)


    • Edited by Almir VukMVP Friday, May 27, 2016 8:54 PM
    • Marked as answer by deskcheck1 Thursday, June 2, 2016 12:31 PM
    Friday, May 27, 2016 8:51 PM
  • Hi,

    Sorry for delayed response...too many projects at the same time.

    Based on your suggestions and further reading from SQL Server online book, I realized that a login is used mainly for granting connection rights to the server; hence, it's mapped to the master database.  Then each authenticated user is granted access rights (select, update, etc.) to specific database tables.

    So I created a single user login account that is used to connect to the server; this login account has no access rights to any database; just to connect to the server.  Since all our users are not within the same office--scattered offices all over the state--we have to use SQL Server authentication (with SQL Server membership provider, etc.).  I created users without logins for those users that need to access certain tables in our databases.

    We're testing this with actual users right now...so, I'm hoping this works.

    Thanks for your responses.


    Marilyn Gambone

    Thursday, June 2, 2016 12:31 PM
  • Hi,

    I've tested what I've done (creating users without logins) and it's not an optimum solution.  I re-read what you wrote here and I followed your suggestions.  I added users to a role in each database and then grant the role the permissions needed.

    This works best for me.

    Thanks.


    Marilyn Gambone

    • Marked as answer by deskcheck1 Monday, June 6, 2016 2:26 PM
    Monday, June 6, 2016 2:26 PM