Prev: SQL Server Drops connection after long timeout with replication partner
Next: Index on a Timestamp Column
From: Patrick on 9 Mar 2010 03:31
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
From: Tibor Karaszi on 9 Mar 2010 05:40
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(a)discussions.microsoft.com> wrote in message
news:OzPYqM2vKHA.3408(a)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

From: jgurgul on 9 Mar 2010 05:47
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
> .
>
From: jgurgul on 9 Mar 2010 05:52
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
> > .
> >
From: jgurgul on 9 Mar 2010 06:00
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
> > > .
> > >
 |  Next  |  Last
Pages: 1 2
Prev: SQL Server Drops connection after long timeout with replication partner
Next: Index on a Timestamp Column