May 23, 2011 at 9:02 am
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
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
Thanks. I had tried that before, but to be thorough, I just tried it again. Same error.
May 23, 2011 at 11:23 am
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
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