Grant SELECT on MsSqlSystemResource SQL2005 5th system DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sigge Persson - 15 Jul 2008 11:25 GMT

Hi.

I am working with an account that has limited access to the SQL server.
Using Sharepoints API, I am opening a list on a web, and get the following
error.
________
The SELECT permission was denied on the object ‘syscolumns’, database
‘mssqlsystemresource’, schema ‘sys’
________

I now need to find a way to grant this account permission to preforn Select
queries on that database.
The account has Server role 'PUBLIC' only.

the two ways i could think of are :
1) Set a higher server role.
and
2) USE Master GRANT SELECT ON sys.syscolumns TO PUBLIC

But the customer dont want to use those solutions, since it isn't secure
enough.

Any other suggestions on how i can resolve that?
As a note, the mssqlsystemresource is a hidden database that complements the
master db. (it contains stored procedures , views and functions)

Reply to this Message

Dan Guzman - 15 Jul 2008 13:40 GMT

> I am working with an account that has limited access to the SQL server.
> Using Sharepoints API, I am opening a list on a web, and get the following
[quoted text clipped - 3 lines]
> ‘mssqlsystemresource’, schema ‘sys’
> ________

I don't know much about Sharepoint but the error indicates that at least
VIEW DEFINITION is required. This permissions allows retrieval of meta-data
related to database objects.

> I now need to find a way to grant this account permission to preforn
> Select
> queries on that database.
> The account has Server role 'PUBLIC' only.

Check out the db_datareader database role. You can also grant permissions
at the schema level. For example:

GRANT SELECT ON SCHEMA::dbo TO SomeAccount

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> Hi.
>
[quoted text clipped - 23 lines]
> the
> master db. (it contains stored procedures , views and functions)

Reply to this Message

Sigge Persson - 15 Jul 2008 15:06 GMT

Thanks.
This will probably solve my problem, i'll give it a try tomorrow.

Thanks for the help.

> > I am working with an account that has limited access to the SQL server.
> > Using Sharepoints API, I am opening a list on a web, and get the following
[quoted text clipped - 45 lines]
> > the
> > master db. (it contains stored procedures , views and functions)

Reply to this Message