GRANT VIEW ANY DEFINITION not Working

Question

GRANT VIEW ANY DEFINITION not Working

homebrew01

SSC Guru

Points: 55548
More actions
September 27, 2010 at 3:07 pm

#229762

I've read that I should be able to run this, and have the user able to see the definition of stored procedures across all databases
USE master
GO
GRANT VIEW ANY DEFINITION TO [MyDomain\MySQL_WindowsGroup]
However when I run SP_HELPROTECT in one of the databases, I do not see VIEW DEFINITION in the ACTION column.
If I run this in one specific database:
use Database_1
GRANT VIEW DEFINITION TO [MyDomain\MySQL_WindowsGroup]
It can see the result with SP_HELPROTECT in the Database_1
Am I doing something wrong, or misunderstanding something (wouldn't be the first time !)

Viewing 12 posts - 1 through 11 (of 11 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Referring to http://www.mssqltips.com/tip.asp?tip=1593 - you would think that is the case. I haven't had success with it either.

Does your windows group already exist with permissions in each database?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

You probably won't see it at the database level because it's a server-level permission. Also, I believe that was new in 2005, so it won't show up there. You'd have to query sys.database_permissions to see permissions that didn't exist in 2000.

edit: to clarify, if you grant view definition on a particular object, it will show up. If you grant view any definition, it acts like a server role and won't.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

I also just ran back through this and tested for myself. The behavior exhibited definitely does not follow the article.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

The windows group has "public" permission on the database. It worked on that same database indivudually, just not globally.

(2005, SP3)

Derrick Smith SSChampion Points: 10833 More actions · Answer 5

CirquedeSQLeil (9/27/2010)
I also just ran back through this and tested for myself. The behavior exhibited definitely does not follow the article.

The article is worded poorly..it says VIEW DEFINITION was granted, not VIEW ANY DEFINITION.

You can either GRANT VIEW DEFINITION TO [username] for all objects in the current database (which will show up in sp_helprotect and sys.database_permissions), or you can GRANT VIEW ANY DEFINITION TO [username] for all databases on the server (which acts as a server role, similar to sysadmin, and won't show up in individual database permissions).

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

So maybe it does work ? ... just can't verify it with sp_helprotect ?

Is there a way to see after the fact what was done ?

I'll have to test the actual functionality later.

Derrick Smith SSChampion Points: 10833 More actions · Answer 7

homebrew01 (9/27/2010)
So maybe it does work ? ... just can't verify it with sp_helprotect ?
Is there a way to see after the fact what was done ?
I'll have to test the actual functionality later.

I don't know of any way to see whether or not it was applied, other than testing. I have never had an issue with it not working in the few hundred times I've granted it though.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Here is how you can now confirm whether or not the permissions granted have been applied:

Use master

GO

SELECT sp.permission_name, p.name

FROM sys.server_permissions sp

Inner Join sys.server_principals p

On p.principal_id = sp.grantee_principal_id

Where sp.permission_name = 'View Any Definition'

GO

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Derrick Smith SSChampion Points: 10833 More actions · Answer 9

CirquedeSQLeil (9/27/2010)
Here is how you can now confirm whether or not the permissions granted have been applied:
Use master
GO
SELECT sp.permission_name, p.name
FROM sys.server_permissions sp
Inner Join sys.server_principals p
On p.principal_id = sp.grantee_principal_id
Where sp.permission_name = 'View Any Definition'
GO

Ahh, I haven't ever really dug into the server_permissions table. That will come in handy.

Thanks 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Derrick Smith (9/27/2010)
CirquedeSQLeil (9/27/2010)
Here is how you can now confirm whether or not the permissions granted have been applied:
Use master
GO
SELECT sp.permission_name, p.name
FROM sys.server_permissions sp
Inner Join sys.server_principals p
On p.principal_id = sp.grantee_principal_id
Where sp.permission_name = 'View Any Definition'
GO
Ahh, I haven't ever really dug into the server_permissions table. That will come in handy.
Thanks 🙂

Thanks to you too - you clarified the issue quite handily.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

BTW - here's another good resource on the topic.

http://www.sql-server-performance.com/articles/dba/Analyzing_SQL_Server_Permissions_p1.aspx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events