View Via Crystal Reports Listing Extra Objects!

[Skittle]

I have set up a role for users who are to have access to a single standard view in my SQL Server database. I have allocated permissions an the users can see and use the view

However in Crystal reports they also see INFORMATION_SCHEMA and SYS as well as the view they should be using. How can I remove these two headers and the many objects listed below them. Denying permissions for each one will take me ages!



Dazed and confused

 

[mrdenny]

DENY the users rights to these schemas. That will override the rights at the object level.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Skittle]

Thats waht I figured but the deny to the role they all belong to has not had the required effect.

Dazed and confused

 

[Skittle]

I have found this command will hide all the objects inside the schemas but it will not hide the schema it's self.

DENY SELECT, UPDATE, ALTER ON SCHEMA::INFORMATION_SCHEMA TO MyUser

Dazed and confused

 

[Skittle]

Gosh Darn It...I spoke to soon. The above command has not hidden the objects.


Dazed and confused

 

[Skittle]

The plot thickens....

If I use a file connection that is OLEDB instead of ODBC, the INFORMATION_SCHEMA and SYS schemas are displayed but you cannot see the objects.

Still a bit annoying I cannot exclude the schema headings from the connection but I can't think of anything else to try.

Dazed and confused

 

[mrdenny]

Try DENY VIEW DEFINATION to the schema. There's some other right listed in there somewhere.

Try the code in here: thread962-1324602 to see what the rights are.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Skittle]

Sadly I cannot get the schema itself to vanish.
The objects below yes, but the schema?....no dice.

DENY CONTROL, ALTER, EXECUTE, INSERT, DELETE, UPDATE, SELECT, REFERENCES, VIEW DEFINITION ON SCHEMA::INFORMATION_SCHEMA TO MyUser

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[mrdenny]

Fire up SQL Profiler and see what commands it's running to get that list. This will help figure out what needs to have the rights removed from.

If you could post the list of commands I'll take a look at it.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[Skittle]

Very kind of you.

Do you mean run the profiler on the connection from Crystal or the stored procedure execution?

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[Skittle]

After lots of pain, I found a posting in a forum that does the deed.

It's a bit poor but it seems to be the only option.
The Extra schemas that are displayed on the ODBC connection are from the master database. By default, public users are allowed to SELECT from them.
The following code simply revoke's that ability.

-- Routine to revoke select permissions for the public role
-- Information_Schema and sys schema views. The statement --- immediately below generates
-- the list of revoke statements following.

use master;
go;
/*
SELECT ' REVOKE SELECT ON '+ s.name + '.' + o.name + ' TO public' 
FROM sys.all_views o, sys.schemas s
WHERE o.schema_id = s.Schema_id
AND o.SCHEMA_ID IN (3, 4) 
ORDER BY s.name, o.name
*/

Dazed and confused.

Remember.. 'Depression is just anger without enthusiasum'.

 

[mrdenny]

I meant on the connection that Crystal makes.

Glad you found the solution.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog