That @*!! Information_Schema
-
I know a similar post has been submitted previously and I tried to pick the eyes out of the responses to help with my problem - er - challenge, but to no avail ! I hope I can get some help with this - SQL Server 2005 by the way.
I have a selection of tables that form the basis of a series of performance and statistical reports. I also make a subset of these tables available to staff who may want to link them to MS Access and query them in their own way. Everything is fine except for one annoying thing. When I open Access and connect using ODBC as a specific user that just happens to be a member of the public role by default, I can see all my tables PLUS all the views in the INFORMATION_SCHEMA and the sys schema. I have tried all sorts of things to remove the access to these and can only achieve it by manually denying select to the public role on all views - and there are quite a few. There must be a way to do this using transact SQL but I'm damned if I can find it. I have dragged my body across miles and miles of barren internet sites in my search - but no luck. Basically all I want to do is to deny permissions to all infomation_schema and sys schema views to the public role. Can anyone help here or just point me in the direction of a good resource for user security and permissions.
My eternal gratitude is awating ....
Best Regards
Terry -
I haven't tried this, but the 'sys' and 'INFORMATION_SCHEMA' schema_id numbers are 3 and 4 respectively, so you might try somthing like this, paste the output into another query and run it:
SELECT
' REVOKE SELECT ON ' + s.name + '.' + o.name + ' TO public'
FROM
sys.all_objects o, sys.schemas s
WHERE
o.SCHEMA_ID IN (SCHEMA_ID('sys'), SCHEMA_ID('INFORMATION_SCHEMA'))
ORDER
BY s.NAME
This will get you everything so you may want to edit the results.
I hope this helps.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
I just found sys.all_views which is probably better for your purposes rather than sys.all_objects
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
Thanks MG, with a couple of modifications to select only the views from INFORMATION_SCHEMA and sys it worked a treat. There was one gotcha of course - you cannot revoke select permissions to sys.sysdatabases, but that's OK.
Thanks again
Best Regards
Terry
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply