There is a request to grant privilege to a particular user for viewing 
stored procedure source code for all databases.

We searched the web and applied the following script:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

Is there any way to check the view definition has been granted ?  We 
have tried sp_helprotect for one of those databases but there is no 
action shown as VIEW DEFINITION.

Your advice is sought.

Thanks

Check out the sys.server_permissions catalog view.

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"Patrick" <Patrick@discussions.microsoft.com> wrote in message 
news:OzPYqM2vKHA.3408@TK2MSFTNGP06.phx.gbl...
> There is a request to grant privilege to a particular user for viewing 
> stored procedure source code for all databases.
>
> We searched the web and applied the following script:
>
> USE master
> GO
> GRANT VIEW ANY DEFINITION TO User1
>
> Is there any way to check the view definition has been granted ?  We have 
> tried sp_helprotect for one of those databases but there is no action 
> shown as VIEW DEFINITION.
>
> Your advice is sought.
>
> Thanks 

Hi Patrick

Something like:

SELECT 
[permission_name],
[state_desc],
[name]
FROM
sys.database_permissions AS prmssn
INNER JOIN sys.database_principals AS grantee_principal 
ON grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE prmssn.class = 0

Jon

"Patrick" wrote:

> There is a request to grant privilege to a particular user for viewing 
> stored procedure source code for all databases.
> 
> We searched the web and applied the following script:
> 
> USE master
> GO
> GRANT VIEW ANY DEFINITION TO User1
> 
> Is there any way to check the view definition has been granted ?  We 
> have tried sp_helprotect for one of those databases but there is no 
> action shown as VIEW DEFINITION.
> 
> Your advice is sought.
> 
> Thanks
> .
> 

I have double checked sp_helprotect and it is giving me a listing for View 
Definition as I would expect.

Jon

"jgurgul" wrote:

> Hi Patrick
> 
> Something like:
> 
> SELECT 
> [permission_name],
> [state_desc],
> [name]
> FROM
> sys.database_permissions AS prmssn
> INNER JOIN sys.database_principals AS grantee_principal 
> ON grantee_principal.principal_id = prmssn.grantee_principal_id
> WHERE prmssn.class = 0
> 
> Jon
> 
> "Patrick" wrote:
> 
> > There is a request to grant privilege to a particular user for viewing 
> > stored procedure source code for all databases.
> > 
> > We searched the web and applied the following script:
> > 
> > USE master
> > GO
> > GRANT VIEW ANY DEFINITION TO User1
> > 
> > Is there any way to check the view definition has been granted ?  We 
> > have tried sp_helprotect for one of those databases but there is no 
> > action shown as VIEW DEFINITION.
> > 
> > Your advice is sought.
> > 
> > Thanks
> > .
> > 

Ahh server wide. :P

SELECT 
[permission_name],
[state_desc],
[name]
FROM
sys.server_permissions AS prmssn
INNER JOIN sys.server_principals AS grantee_principal ON 
grantee_principal.principal_id = prmssn.grantee_principal_id
WHERE
(prmssn.class = 100)

Jon

"jgurgul" wrote:

> I have double checked sp_helprotect and it is giving me a listing for View 
> Definition as I would expect.
> 
> Jon
> 
> "jgurgul" wrote:
> 
> > Hi Patrick
> > 
> > Something like:
> > 
> > SELECT 
> > [permission_name],
> > [state_desc],
> > [name]
> > FROM
> > sys.database_permissions AS prmssn
> > INNER JOIN sys.database_principals AS grantee_principal 
> > ON grantee_principal.principal_id = prmssn.grantee_principal_id
> > WHERE prmssn.class = 0
> > 
> > Jon
> > 
> > "Patrick" wrote:
> > 
> > > There is a request to grant privilege to a particular user for viewing 
> > > stored procedure source code for all databases.
> > > 
> > > We searched the web and applied the following script:
> > > 
> > > USE master
> > > GO
> > > GRANT VIEW ANY DEFINITION TO User1
> > > 
> > > Is there any way to check the view definition has been granted ?  We 
> > > have tried sp_helprotect for one of those databases but there is no 
> > > action shown as VIEW DEFINITION.
> > > 
> > > Your advice is sought.
> > > 
> > > Thanks
> > > .
> > > 

Many thanks for your help.  Much appreciated.


On 9/03/2010 10:00 PM, jgurgul wrote:
> Ahh server wide. :P
>
> SELECT
> [permission_name],
> [state_desc],
> [name]
> FROM
> sys.server_permissions AS prmssn
> INNER JOIN sys.server_principals AS grantee_principal ON
> grantee_principal.principal_id = prmssn.grantee_principal_id
> WHERE
> (prmssn.class = 100)
>
> Jon
>
> "jgurgul" wrote:
>
>> I have double checked sp_helprotect and it is giving me a listing for View
>> Definition as I would expect.
>>
>> Jon
>>
>> "jgurgul" wrote:
>>
>>> Hi Patrick
>>>
>>> Something like:
>>>
>>> SELECT
>>> [permission_name],
>>> [state_desc],
>>> [name]
>>> FROM
>>> sys.database_permissions AS prmssn
>>> INNER JOIN sys.database_principals AS grantee_principal
>>> ON grantee_principal.principal_id = prmssn.grantee_principal_id
>>> WHERE prmssn.class = 0
>>>
>>> Jon
>>>
>>> "Patrick" wrote:
>>>
>>>> There is a request to grant privilege to a particular user for viewing
>>>> stored procedure source code for all databases.
>>>>
>>>> We searched the web and applied the following script:
>>>>
>>>> USE master
>>>> GO
>>>> GRANT VIEW ANY DEFINITION TO User1
>>>>
>>>> Is there any way to check the view definition has been granted ?  We
>>>> have tried sp_helprotect for one of those databases but there is no
>>>> action shown as VIEW DEFINITION.
>>>>
>>>> Your advice is sought.
>>>>
>>>> Thanks
>>>> .
>>>>