April 13, 2012 at 6:14 pm
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?
April 16, 2012 at 8:38 am
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
April 16, 2012 at 4:59 pm
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
April 16, 2012 at 6:10 pm
Thanks so much for the tips. I ended up going with the guest user option and it's working great.
September 14, 2018 at 1:48 pm
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
September 14, 2018 at 11:20 pm
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply