CODEWITHSUNDEEP
sqlsql-servert-sqlsecuritysql-server-2008-r2
Avi
Avi

Reputation: 1193

Deny VIEW DEFINITION permission to SYS schema

I have a just created new user in a database. Its not part of any role, neither built in, neither user defined.

If I view its effective permission on [sys] schema objects:

EXECUTE AS LOGIN = 'Foo';
SELECT * FROM fn_my_permissions('[sys].[sysobjects]', 'OBJECT');

I see that it can actually VIEW DEFINITION for SYS schema objects:

entity_name subentity_name  permission_name
[sys].[sysobjects]      SELECT
[sys].[sysobjects]      VIEW DEFINITION

Maybe VIEW ANY DEFINITION is granted for public on server level? Why is this there?

Main question is even better, if I issue a DENY VIEW DEF:

DENY VIEW DEFINITION ON SCHEMA::sys to Foo;

It won't go away, and the user can still view a SYS OBJECT definition.

How can I prevent user from VIEW DEFINITION of SYS schema objects.

Thanks, Joe

Upvotes: 0

Views: 1429

Answers (1)

Avi
Avi

Reputation: 1193

Found the answer in the depths of MSDN: https://msdn.microsoft.com/en-us/library/ms176112(v=sql.105).aspx

Permissions Requires membership in the public role. System object definitions are publicly visible. The definition of user objects is visible to the object owner or grantees that have any one of the following permissions: ALTER, CONTROL, TAKE OWNERSHIP, or VIEW DEFINITION.

Upvotes: 0

Related Questions