patternsqlModerate
Deny access to information schema in SQL Server
Viewed 0 times
sqldenyaccessserverschemainformation
Problem
I am looking for the best way to disable access to the
I found this thread from 2008
It shows a way how to deny access on
But no way how to disable access on the
This seems not to work.
How can I disable access to information_schema?
And is there an easier way disable access to all
Update:
Actually I can not run both ot the following statements:
I tried to run them on the specific DB where the User exists, and I also tried on the "master".
I still can run:
-->still returns results
-->no results anymore
Including
But now I still can select all the information from the DB.
I had a look at the "Securables"-Tab in the users Property-window in Management Studio 2008, it looks like this:
Entry that does block the selecion of sys.tables
Schema:sys, Name:tables, Type:View
Permissions for sys.tables: Permission:Select, Grantor:dbo, Deny is checked
Entry that do not block any selection
Schema:, Name:INFORMATION_SCHEMA, Type:Schema
Permissions for INFORMATION_SCHEMA: Permission:Select, Grantor:dbo, Deny is NOT checked (I tried to check it, but no chance..)
Permission:Select, Grantor:INFORMATION_SCHEMA, D
sys.tables / Information Schema for a user / group in SQL Server.I found this thread from 2008
It shows a way how to deny access on
[sys].[something] like so:DENY SELECT ON [sys].[columns] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[tables] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[syscolumns] TO DenySystemTableSelectRole
GO
DENY SELECT ON [sys].[sysobjects] TO DenySystemTableSelectRole
GOBut no way how to disable access on the
Information Schema:DENY SELECT ON INFORMATION_SCHEMA.TABLES To DenySystemTableSelectRoleThis seems not to work.
How can I disable access to information_schema?
And is there an easier way disable access to all
sys / information_schema?Update:
Actually I can not run both ot the following statements:
DENY SELECT ON [sys] TO reducedDBO
GO
DENY SELECT ON INFORMATION_SCHEMA To reducedDBO
GOI tried to run them on the specific DB where the User exists, and I also tried on the "master".
I still can run:
SELECT * from
INFORMATION_SCHEMA.TABLES-->still returns results
SELECT * from
sys.TABLES-->no results anymore
Including
SCHEMA:: in the query made it possible to create the securablesDENY SELECT ON SCHEMA::[sys] TO reducedDBO
GO
DENY SELECT ON SCHEMA::INFORMATION_SCHEMA To reducedDBO
GOBut now I still can select all the information from the DB.
I had a look at the "Securables"-Tab in the users Property-window in Management Studio 2008, it looks like this:
Entry that does block the selecion of sys.tables
Schema:sys, Name:tables, Type:View
Permissions for sys.tables: Permission:Select, Grantor:dbo, Deny is checked
Entry that do not block any selection
Schema:, Name:INFORMATION_SCHEMA, Type:Schema
Permissions for INFORMATION_SCHEMA: Permission:Select, Grantor:dbo, Deny is NOT checked (I tried to check it, but no chance..)
Permission:Select, Grantor:INFORMATION_SCHEMA, D
Solution
You should be able to just deny permissions on the entire
That should basically just prevent that user from doing any selects in those two schemas.
sys and information_schema schema as a whole:DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]That should basically just prevent that user from doing any selects in those two schemas.
Code Snippets
DENY SELECT On SCHEMA::sys To [user_name]
DENY SELECT On SCHEMA::INFORMATION_SCHEMA To [user_name]Context
StackExchange Database Administrators Q#25667, answer score: 11
Revisions (0)
No revisions yet.