locked
Permission denied on sys schema from approle RRS feed

  • Question

  • I have an application that is connection to the database using an application role.
    The application role has been granted CONTROL.

    Everything works, except access to the sys schema (by example: select * from sys.columns).

    How do I grant permission on the sys schema for an application role?
    All exampes I can find are about granting this to a user - I can not find how to do it for an application role.

    Please help.

    Monday, October 15, 2012 3:23 PM

Answers

  • @Hima:

    I only use stored procedures, but I think certificates are big overkill.

    @Russel:

    The error is:

    Msg 229, Level 14, State 5, Line 1
    The SELECT permission was denied on the object 'syscolumns', database 'mssqlsystemresource', schema 'sys'.

    Because of this error I assumed that the sys views gave the problem, but after your statement I have checked all sys views that I use, and I found out that only 1 is giving a problem: sys.syscolumns.

    I now rewrote the SQL code to use sys.columns instead of sys.syscolumns.

    The problem is now solved - thank you!

    • Edited by atverweij Tuesday, October 16, 2012 7:10 AM
    • Marked as answer by atverweij Tuesday, October 16, 2012 7:19 AM
    Tuesday, October 16, 2012 6:44 AM

All replies

  • Hello,

    It is not a good idea to let the application roles query the system tables. You might rather want to get the results through stored procedures and grant the access through certificates. Please take a look at http://www.sommarskog.se/grantperm.html#Certificates


    Hima, To help the community better identify relevant thread, please don't forget to mark as answer or vote as helpful if and answer helps.

    Monday, October 15, 2012 8:23 PM
  • Could you provide some more information on the error?  Are you trying to select from sys.columns in another database?  If so, the scope of an application role is within the database.  (Some exceptions for certain server level sys views.)  I tried the following script and all worked fine for me.

    use testdb
    go
    create application role confuse with password = '&^*654abGD'
    go
    grant select on schema::sys to confuse
    go
    DECLARE @Cookie varbinary(8000)
    exec sp_setapprole 'confuse', '&^*654abGD',@fCreateCookie = true, @cookie = @cookie OUTPUT;
    Select SUSER_SNAME(), USER_NAME() 
    select * from sys.columns 
    select * from sys.databases
    select * from sys.server_principals
    exec sp_unsetapprole @cookie
    go
    drop application role confuse
    go
    Please let us know,
    RLF
    Monday, October 15, 2012 8:31 PM
  • @Hima:

    I only use stored procedures, but I think certificates are big overkill.

    @Russel:

    The error is:

    Msg 229, Level 14, State 5, Line 1
    The SELECT permission was denied on the object 'syscolumns', database 'mssqlsystemresource', schema 'sys'.

    Because of this error I assumed that the sys views gave the problem, but after your statement I have checked all sys views that I use, and I found out that only 1 is giving a problem: sys.syscolumns.

    I now rewrote the SQL code to use sys.columns instead of sys.syscolumns.

    The problem is now solved - thank you!

    • Edited by atverweij Tuesday, October 16, 2012 7:10 AM
    • Marked as answer by atverweij Tuesday, October 16, 2012 7:19 AM
    Tuesday, October 16, 2012 6:44 AM