Allowing *ALL* users to access a database

  • captbob007

    Old Hand

    Points: 386

    I didn't think opening a database up to all users would be this hard, but I'm getting nowhere fast so I figure I'll post here.

    I have a database that I want to allow anyone to select from certain tables. To clarify, by "anyone", I mean anyone that can already login to the server. There are other databases I want to remain secured, but this one, named "training" I want to be accessible to all. I'm using SQL Server 2008 R2 SP1 if you're curious.

    I have granted SELECT and VIEW DEFINITION rights on the appropriate tables to the public role, and thought that would be it. What I now see is that users can connect and select from those tables, however they can't expand the database in Object Explorer to see a list of what the tables are - they get an error saying the database is not accessible when they try.

    I tried granting VIEW DEFINITION rights to public at the database level and this had no effect either. Anyone have any ideas what I'm doing wrong?

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    captbob007 (4/13/2012)


    I didn't think opening a database up to all users would be this hard, but I'm getting nowhere fast so I figure I'll post here.

    I have a database that I want to allow anyone to select from certain tables. To clarify, by "anyone", I mean anyone that can already login to the server. There are other databases I want to remain secured, but this one, named "training" I want to be accessible to all. I'm using SQL Server 2008 R2 SP1 if you're curious.

    I have granted SELECT and VIEW DEFINITION rights on the appropriate tables to the public role, and thought that would be it. What I now see is that users can connect and select from those tables, however they can't expand the database in Object Explorer to see a list of what the tables are - they get an error saying the database is not accessible when they try.

    I tried granting VIEW DEFINITION rights to public at the database level and this had no effect either. Anyone have any ideas what I'm doing wrong?

    Typically when a person is denied access to the database through Object Explorer with a "not accessible" error message it means they do not have a DATABASE USER mapped to their Server Login in that database, and they are not a sysadmin or the dbo. But you go on to say they can SELECT from the tables in question, which does not compute. Have you created a Database User for each Server Login that you want to have access?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    If you know for a fact that you want every login that is granted access to the SQL Server to access this database and that's not going to change (or you'll have time to make a fix when it has to change), here's the simplest solution:

    - Create a role in that database that has the permissions needed by all

    - Enable the guest user in that database

    - Make the guest user a member of the role you have created

    The guest user will be the user a login will use if they don't already have access to the database. So if you've explicitly granted access to other logins, you'll want to make sure they are members of that role as well.

    The reason I'm not recommending that you give permission to public is in the event that you did have to manage a change in permissions, you could easily create another role, grant the specific login access to the database, and make it a member of that role.

    K. Brian Kelley
    @kbriankelley

  • captbob007

    Old Hand

    Points: 386

    Thanks so much for the tips. I ended up going with the guest user option and it's working great.

  • Tony Trus

    SSChasing Mays

    Points: 654

    Old post but wanted to clarify for those who search this on google that the bare minimum one could use (assuming only one object is needed) is this:

    USE SOME_DB
    GO
    GRANT CONNECT TO GUEST
    GRANT INSERT on dbo.logins TO public

    I arbitrarily chose insert privilege and this can be whatever is needed.  A good use case for this is for a server level trigger to audit logins and have to assume that some accounts connecting have minimal privileges.   With the above in place, a logon trigger would be able to populate a table with relevant information such as HOST_NAME(), PROGRAM_NAME() , SUSER_SNAME(), GETDATE() or any other relevant info.  
    -T

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    Hi T, have you looked at the pattern offered in the Microsoft docs on logon triggers? https://docs.microsoft.com/en-us/sql/relational-databases/triggers/logon-triggers?view=sql-server-2017

    Specifically pay attention to the EXECUTE AS clause. I think approaching your trigger definition this way could help you avoid granting anything to public which would be an improvement in your instance security.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply