Post reply

User cannot see Stored Procedure

  • May 23, 2011 at 9:02 am

    #236775

    Environment (Dev)

    - SQL 2008 SP2

    - Windows Ent 2008 SP2 64-bit

    This is for a data warehouse environment. We use stored procedures to create datasets for reports (SSRS). Currently, data sources for these reports use a generic AD login which has been given permissions in the appropriate places to allow connections. However, we want to use AD users (or users within AD Groups) to make their own connections so we can be more granular with security of the various data which is being integrated.

    Problem

    The issue remaining is that an error is received when running the report (from SSRS Report Manger) that the user cannot see the stored procedure:

    Cannot find the object 'StoredProcedureName', because it does not exist or you do not have permission.

    Permissions to Execute have been granted to Public:

    GRANT EXEC ON [DBname].[dbo].[StoredProcedureName] TO PUBLIC

    User/Group will run report fine (from SSRS Report Manger) when given CONTROL or Admin type role at DB or Object level. However, we don't want to extend that much permission to these users. What am I missing that is preventing these users from seeing/accessing the SPs?

  • May 23, 2011 at 9:53 am

    #1328537

    Under database\security\users go to Securables. Add stored procedure you need and grant execute permmision for the user.

  • May 23, 2011 at 10:04 am

    #1328548

    Thanks. I had tried that before, but to be thorough, I just tried it again. Same error.

  • May 23, 2011 at 11:23 am

    #1328612

    Sounds like a DENY may be in place.

    What does this return?

    USE [DBname]

    GO

    SELECT perms.*,

    users.name

    FROM sys.database_permissions perms

    JOIN sys.database_principals users ON perms.grantee_principal_id = users.principal_id

    WHERE perms.major_id = OBJECT_ID(N'[dbo].[StoredProcedureName]') ;

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 23, 2011 at 11:38 am

    #1328625

    No 'Deny' on that object. But I'm going to use that approach on other objects further up the chain and see what I get. Be back after that...

    Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply