September 18, 2006 at 7:17 pm
I need to create a new login that can only see 2 Views.
I have tried everything, but when I connect to the server with MS Access or MS Excel, and sign in using that Login, I can see bunch of views. I only want this Login to be able to run either view and not see anything else.
This is what I have done so far:
1) Created Login zzz (w/ SQL Ser Auth)
2) Set the default database to database abc (Nothing is selected in Server Roles)
3) Set User Mapping to database abc, user = zzz
4) Database roll membership is public. (It won't let me change it)
5) Added user zzz to the two Views (Granted Select only)
6) Connect to the server from Excel, select new database query, add new data source (connection tests successful)
When I go to select the default table I can see the two views, but I can also see a bunch of other views. (all_columns, all_objects, etc..)
It looks like it's everything in the System Views Folder. (Not only can I see the other views, I can query them)
I can individually remove the Public User from each view in the System View folder and it disappears from the default table list.
Do I need to do that for each item in the system views folder? There has to be upwards of 200 views. Anyway to change them all at once?
And then there is the views listed as Information_Schema, the public user isn't setup on those. I can't find a way to get rid of these.
September 19, 2006 at 5:10 am
Run the following query, which will generate the SQL to change the permissions all at once. Be careful, because if you have any DENYs on any of the views, they will be revoked, too.
John
SELECT 'REVOKE SELECT ON ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' FROM public'
FROM information_schema.tables
WHERE TABLE_TYPE = 'VIEW'
September 19, 2006 at 2:12 pm
Public role has Select permission on these views. Every user is a member of Public. If you don't want these 2 people see info in these views and you don't revoke Select from public as John says (see the previous post) then another solution is to create views in a new database that point to your data in your database. You will have to manage permissions on these views with regards to cross database ownership chaining.
Regards,Yelena Varsha
September 20, 2006 at 7:41 am
Thanks for your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply