view definition of sys.syscomments

Claudia

unread,

Feb 12, 2010, 5:03:01 PM2/12/10

to

We use sql2005. We have a script auditing tool that needs access to view the
definition of sys.syscomments. To enable the permission, I ran the statements
listed below but it still got denied.

use master
go
grant view server state to [xxUserName]
grant view any definition to [xxUserName]
grant view any definition to public
grant select on sys.syscomments to public

The error message from auditing tool: The SELECT permission was denied on
the object 'syscomments', database 'mssqlsystemresource', schema 'sys'.

Then I tried Management Studio. I double clicked the user account and under
Securables, I added sys.syscomments and checked "View definition". clicking
OK brought up an error : Grant failed for View
'sys.syscomments'........Granted or revoked privilege VIEW DEFINITION is not
compatible with object. (Microsoft SQL Server, Error: 4606)

If I go to Logins, double click the login name, under Scurables, click Add
and choose "The server 'myProdServer'", it does show this login has "View
server state" and "View any definition" checked. This login has permission to
connect to database engine and is not disabled.

Could anyone help? Am I missing anything?

Thanks!

Erland Sommarskog

unread,

Feb 12, 2010, 5:59:29 PM2/12/10

to

Claudia (Cla...@discussions.microsoft.com) writes:
> We use sql2005. We have a script auditing tool that needs access to view
> the definition of sys.syscomments. To enable the permission, I ran the
> statements listed below but it still got denied.
>
> use master
> go
> grant view server state to [xxUserName]
> grant view any definition to [xxUserName]
> grant view any definition to public
> grant select on sys.syscomments to public
>
> The error message from auditing tool: The SELECT permission was denied on
> the object 'syscomments', database 'mssqlsystemresource', schema 'sys'.

I think you need to use Profiler to find out what SQL statements
the tool generates when it gets this error. You can include the
events Error:Exception and Error:UserMessage in the trace.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Kalen Delaney

unread,

Feb 12, 2010, 10:31:58 PM2/12/10

to

Hi Claudia

Can you explain exactly what you need to do? Do you need the definition of
sys.syscomments or the contents of sys.syscomments?

If the former, what happens when you just use the preferred SQL 2005 method
which is to select OBJECT_DEFINITION?

SELECT OBJECT_DEFINITION(object_id('sys.syscomments'));

(Make sure you have adjust the results in your query tool to show more than
just 256 characters. )

--
HTH
Kalen
----------------------------------------
Kalen Delaney
SQL Server MVP
www.SQLServerInternals.com

"Claudia" <Cla...@discussions.microsoft.com> wrote in message
news:DFFE1C61-2A7D-46CB...@microsoft.com...

Uri Dimant

unread,

Feb 14, 2010, 2:41:48 AM2/14/10

to

Claudia
What login you are connected to run the script? Is that possible that
someone played with public role?

"Claudia" <Cla...@discussions.microsoft.com> wrote in message
news:DFFE1C61-2A7D-46CB...@microsoft.com...