View definition permission issue

Question

View definition permission issue

WayneS

SSC Guru

Points: 95460
More actions
March 30, 2010 at 10:34 am

#146246

I have two sql 2005 instances installed on the same computer... they are both development instances, of different versions of the databases.
Both instances have the same logins and database users. They both have a sql login (I'll call it "USER_A"). On both instances, this login belongs to the "BULKADMIN" and "public" server roles. In the database, the user belongs to the "db_datareader", "db_datawriter" and "public" database roles on both instances.
In the first instance, the user can view the definitions of stored procedures (in SSMS, can right-click the procedure and select "Modify"). In the second instance, this user can't ("Modify" is disabled).
In the first instance, when I go to database | security | users and check the user permissions, it shows "VIEW DEFINITION" as an EFFECTIVE permission for the objects (on the Effective tab). This permission is not checked in the 'Explicit' tab. In the second instance, it does not show up on either tab.
Since the first instance is getting this through an effective permission, I checked the server permissions.When I checked sys.server_permissions:
select perm.*
from sys.server_permissions perm
JOIN sys.server_principals sp
ON perm.grantee_principal_id = sp.principal_id
where sp.name = 'USER_A'
All I get on both instances is the "CONNECT SQL" permission for this login.
If I perform a GRANT VIEW DEFINITION ON <object> TO USER_A on the second instance, this permission shows up as an explicit permission.
So, how is this login getting the "View Definition" permission on the first instance through an effective permission?
Thanks for helping out with this!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 6 posts - 1 through 5 (of 5 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Per MSDN:

The effective permissions that a principal has on a securable are the result of the explicit permissions defined for that principal on that securable, the permissions defined on the parents of the securable, and the permissions that the principal inherits through role or group membership. Use the Effective Permissions dialog box to view the permissions that result when all the permissions of a principal on a securable are combined. The Effective Permissions dialog is read-only. Use the Securables pages to change permissions.

So I would guess that the user inherited the permission via one channel or another.

Check the permissions that have been granted to both of the roles it's in.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Paul White SSC Guru Points: 150467 More actions · Answer 2

You are just checking server-level (login) permissions.

Write a similar query to check the database-level (user) permissions.

Use sys.database_permissions and sys.database_principals

select perm.*

from sys.database_permissions perm

JOIN sys.database_principals p

ON perm.grantee_principal_id = p.principal_id

WHERE ...

You can join from sys.database_principals to sys.server_principals on sid, for appropriate types (S, U, K, G - IIRC)

Paul

edit: to add code

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 3

...and if you want a guess, I would say that the VIEW ANY DEFINITION is inherited from the ADMINISTER BULK OPERATIONS permission, since bulk insert requires checking the definition of the target. Strictly a guess, but will look cool if it turns out to be close.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

Last thought:

Check out the super-handy function and examples:

Covering/Implied Permissions (Books Online)

That will show you all implied permissions at any level.

edit: Oh, and check that the database with Modify disabled hasn't been set to read-only 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 5

Paul White NZ (3/30/2010)
You are just checking server-level (login) permissions.
Write a similar query to check the database-level (user) permissions.
Use sys.database_permissions and sys.database_principals
select perm.*
from sys.database_permissions perm
JOIN sys.database_principals p
ON perm.grantee_principal_id = p.principal_id
WHERE ...
You can join from sys.database_principals to sys.server_principals on sid, for appropriate types (S, U, K, G - IIRC)
Paul
edit: to add code

Thanks Paul. This showed me that this user had been granted view definition at the database level.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2