SQL Database Access Based On User Table? RRS feed

  • Question

  • I am designing a database and am creating essentially a users table that gives access to the application. I'm wondering if there is a way to grant access to the database based on the security level assigned to users in the database's users table. So, for instance...

    Users: Nathon, Dalton, 123 Some St, ..., SecurityLevel1 (Admin), ...
    Users: John, Doe, 123 Some Other St., ..., SecurityLevel5 (DB Only), ... -- This would give access to the database only.

    So, is there a way to setup the database so that it will allow/disallow people based on the security level in one of it's own tables?
    Nathon Dalton .NET Software Developer
    Tuesday, November 17, 2009 4:35 PM


  • Hi,

    Do you mean you want to grant appropriate permissions to the uses who stored in a user table based on database security level? If so, you can't do this, because the users are not database users. Even if they are database users,  you also need a SQL Login to map one database user to login the server, otherwise, the users are not accessible. If your application is based on .NET Framework, you can achieve it suing .NET Framework Security. (see here: http://msdn.microsoft.com/en-us/library/aa720329%28VS.71%29.aspx).

    I also suggest you post a thread in developer forums, for example, ASP.NET forum (http://forums.asp.net/25.aspx).

    Hope this helps. If you have any concern, please feel free to let me know.
    ChunSong Feng
    Microsoft Online Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by Alex Feng (SQL) Wednesday, November 25, 2009 10:48 AM
    Friday, November 20, 2009 8:42 AM