Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Grant access to a single table

    So here's the situation: I'm creating a front-end application to an SQL Server 2000 database and I need to create a new login to the database for the app.

    Is it possible (or even; is it a good idea...) to create a logon that only has access to a single table?

    Actually, I know it's possible - but I don't want to have to go through the hundreds of tables and deny access to them all! So I guess the question is;

    Is there a quick and easy way of granting a login permissions to a single table only?
    George
    Home | Blog

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha! I think I might have cracked it...

    Create a new database role and then a new login who can have that role

    However, the question of "should I do this" still stands.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Why not?

    I don't understand what your concern is.

    For a single logon, you could even grant the access directly without bothering with a role.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ooh, how do I do that?
    George
    Home | Blog

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The same way you grant access to a role.

    Remember, though, that every login is a member of the PUBLIC role, so you will need to make sure that PUBLIC does NOT have access to any of the objects in the database.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How did I miss that button before?! Woods and trees, eh.

    The public role only has access to sys tables and dt* SPs; I don't have a problem with that.

    I just figured this was the easiest and most effective way of implementing security on the FE. This is definately a route I will use again in the future

    Thanks Blinddude
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •