Restrict who can see certain views (not just the data)

Question

Post reply

Restrict who can see certain views (not just the data)

matthew lancashire

SSC Enthusiast

Points: 147
More actions
June 30, 2004 at 6:38 am

#102086

I need to create a number of SQL schemas that I will be accessed only by the owners of the schema.
e.g.
MyDB.zippy.View1 must only be seen by user zippy
MyDB.bungle.View1 must only be visible to user bungle
The problem is that although I can prevent the user bungle from accessing the data, they can still see the view in the crystal product.
ie. Users zippy and bungle both see a list that contains bungle.View1 and zippy.View1
It really confuses them and rains on my parade when they complain that 'the list of tables (views) is too cluttered with unneeded entries'
How can I stop these views from schemas owned by different users from being seen by other users?

Viewing 8 posts - 1 through 7 (of 7 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

To be honest - i can't think of a way to do this

in the past when writing bespoke apps i have created a view that runs along the lines of

select name from sysobjects where......

i don't think it's possible if the application reads from the sysobjects table to get the list of tables and views.

how many users do you have using the system?

if it's only a couple using the system, you could set up some shadow databases (replace all tables with views linked to the original datbabase) and don't include the other ownership schemas - to be honest i think this is a very bad idea, but it might give you an idea to work from.

MVDBA

matthew lancashire SSC Enthusiast Points: 147 More actions · Answer 2

The actual database is designed foruse with an MIS application I have written so in that respects it works like a dream because I control the the views they can select from.

Unforntunately there are one or two power users who have crystal that are let's say easily confused. The whole reason for me writing the MIS app was to reduce the need for crystal reports in 90% of cases, but as ever it's the thin end of the wedge who have the loudest voices and thee stampiest feet.

I won't be creating seperate databases as this would create quite a number of them.

Thanks for having a go though.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

i know in crystal reports there are plenty of options for filtering out system views, stored procedures etc etc...

are there such options in business objects? sounds like you coud escalate it to them.!

MVDBA

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Restricting Views should work the same way as restricting Stored Procedures, Tables, etc.

Here's the easiest way, in Enterprise Manager, drill down to the database. Expand the database. Click on Views. Find the View you wish to restrict. Right click on it and select Properties. In the upper right corner is the button Permissions. Click that. For all users that you wish to DENY access, click on the boxes (under SELECT, INSERT, etc) until a RED X shows in each box per user.

A little bit harder, use Query Analyzer and the REVOKE command. Refer to the BOL for more information.

That should stop them from seeing the View exists.

-SQLBill

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

i don't think this was the problem, or have i misunderstood.

isn't the problem making them HIDDEN, so that users don't get confused with so many choices

MVDBA

matthew lancashire SSC Enthusiast Points: 147 More actions · Answer 6

Ta Bill.

I dtried all that but haven't tried the REVOKE command yet.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

revoke command does the same as having no permissions set in enterprise manager.

using the permissions in EM is the same as issuing GRANT,REVOKE and DENY commands, they won't hide your views, just stop you accessing them.

MVDBA