March 25, 2014 at 9:21 am
Hi, I suspect I've missed something obvious, but I can't see it myself. I'm getting the message "The EXECUTE permission was denied on the object 'MyProc', database 'MyDB', schema 'dbo'." when attempting to execute a stored procedure, but it looks as if the correct permission is in place.
1) We're using Windows authentication for users on our databases.
2) All tables and stored procedures are owned by the dbo schema.
The stored procedures are divided into those that only read data and those that amend data. The read-only ones each have Execute permission explicitly granted to 'all domain users'. A particular database user has then been granted Execute permission on the dbo schema which, as I understand it, should allow execution of all stored procedures under that schema. This user is able to execute the read-only procedures with no problems, but gets the above message on all the update ones. I've checked and no DENY permissions appear to be in place. Note: the special database user is a member of 'all domain users' so could be getting access to the read-only procedures that way.
Thanks in advance for any help.
March 25, 2014 at 10:14 am
March 25, 2014 at 10:22 am
No, it's a member of the db_datareader role and the only other permission is the Execute on dbo.
March 31, 2014 at 6:05 am
Is there any other information in the error you're seeing?
I've run into this issue when the procedure is doing activity that requires higher level permissions (such as Truncate Table), but usually there is more detail to the error messages.
March 31, 2014 at 6:41 am
Chris can you take a look at the specific stored proc that is raising the error?
ownership chaining is probably disrupted in this specific case.
you can get that error "The EXECUTE permission was denied on the object 'MyProc', database 'MyDB', schema 'dbo'."
in a couple of scenarios i can think of:
1. someone explicitly denied permissions to the procedure.
2. the procedure touches objects in a different database, or in a different schema, than dbo, which breaks the ownership chaining and required extra permissions to underlying objects in order to work.
if you look at the body of the proc, is it selecting/inserting/updating data from a different database, i'd think that's where the issue is.
Lowell
March 31, 2014 at 8:10 am
Thanks for your response. The error we're getting is shown in the attachment; not much detail as it's being displayed by an old VB6 program. The stored procedure just does a simple delete (see below).
CREATE PROCEDURE [dbo].[DeleteGroupAppRelation]
/* deletes group app relation record for given group ID and application ID */
@intApplicationIDint,
@strGroupNamevarchar (24)
AS
DELETE FROM [HPApps].dbo.tbl_GroupAppRelation
WHERE ApplicationID = @intApplicationID
AND GroupID = (SELECT ID
FROM [HPApps].dbo.tbl_Group
WHERE Name = @strGroupName)
The code to create the table is as follows.
CREATE TABLE [dbo].[tbl_GroupAppRelation](
[GroupID] [int] NOT NULL,
[ApplicationID] [int] NOT NULL,
CONSTRAINT [PK_tbl_GroupAppRelation] PRIMARY KEY CLUSTERED
(
[GroupID] ASC,
[ApplicationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tbl_GroupAppRelation] WITH CHECK ADD CONSTRAINT [FK_tbl_GroupAppRelation_Function] FOREIGN KEY([ApplicationID])
REFERENCES [dbo].[tbl_Application] ([ID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_GroupAppRelation] CHECK CONSTRAINT [FK_tbl_GroupAppRelation_Function]
GO
ALTER TABLE [dbo].[tbl_GroupAppRelation] WITH CHECK ADD CONSTRAINT [FK_tbl_GroupAppRelation_UserGroup] FOREIGN KEY([GroupID])
REFERENCES [dbo].[tbl_Group] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_GroupAppRelation] CHECK CONSTRAINT [FK_tbl_GroupAppRelation_UserGroup]
GO
March 31, 2014 at 8:23 am
the procedure deleted from the database [HPApps], which i suspect is NOT the same db thatt proc exists in.
so if the produdure exists in [LocalDB], the permisisons chain is broken,and the basic user cannot get the proc to work.
the right way to fix it is by signing the procedure with a certificate.
other ways to fix the issue are to use EXECUTE AS int he procedure, as a use rthat exists in BOTH databases, AND has permissions to the udnerlying objects.
Lowell
March 31, 2014 at 8:33 am
I think Lowell's on the right track here. It seems the jumping around has a lot to do with your issues.
If you are able to impersonate the login using this proc in SSMS, you might get a more relevant error message. Just make sure to REVERT after you do this, though, so you don't get accidentally stuck only with those perms.
March 31, 2014 at 8:58 am
The table(s) and stored procedure are definitely in the same database and that is the same database specified by the application when it opens the connection. We've tried explicitly granting execute on the procedure now and get the same message which implies (to me) that there's a DENY somewhere that we can't see. I'll see if I can get impersonation set up so that I can work through SSMS rather than the application as it's possible we're not receiving the true error message.
Thanks both for your input.
Chris
March 31, 2014 at 9:15 am
it's rare that a procedure explicitly names the database while in the same database; i really think it's affecting another database: this snippet from your proc really makes me think it's the case i described.
DELETE FROM [HPApps].dbo.tbl_GroupAppRelation
for the drill down, this might help your identify explicit permissions:
SELECT CASE dbpe.[state] WHEN 'W' THEN 'GRANT'
ELSE dbpe.state_desc COLLATE Latin1_General_CI_AS
END AS [state_desc]
, dbpe.permission_name COLLATE Latin1_General_CI_AS AS perm_name
, sch.name AS sch_name
, OBJECT_NAME(dbpe.major_id) AS TheObject
, dbpr.name AS DBUsername
, CASE dbpe.[state] WHEN 'W' THEN '] WITH GRANT OPTION'
ELSE '' END AS withgrant
FROM sys.database_permissions dbpe INNER JOIN sys.database_principals dbpr
ON dbpr.principal_id = dbpe.grantee_principal_id
INNER JOIN sys.objects obj ON dbpe.major_id = obj.object_id
INNER JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
WHERE obj.type NOT IN ('IT','S','X')
ORDER BY dbpr.name, obj.name
for testing, impersonation is easy:
USE MyDB
EXECUTE AS USER = mydomain\lizaguirre' --must be a user in the database
SELECT * FROM sys.tables --probably see nothing, even though tables really exist
SELECT * FROM sys.procedures --probably sees one proc he has EXECUTe permissions on, although others EXIST
REVERT; --change back into myself/superman
Lowell
March 31, 2014 at 11:50 am
Odd question. Is there perhaps a database trigger that is rolling back any attempts at A) Executing this proc, or B) deleting data from this (or all) tables?
April 1, 2014 at 2:09 am
The stored procedure really is in the same database as the tables; most of our stored procedures use the 3-part name because some operate across more than one database and it avoids confusion. I ran the script you provided to check effective permissions after doing an Execute As the special user and the only permission visible on that stored procedure is the Grant Execute. That said, I can execute it via SSMS when impersonating that user so it appears that the problem must be to do with the way the command is run from the VB application. When I run the two Selects you provided under impersonation, they list all the tables/stored procedures in the database as expected.
I've now logged onto a workstation as that user and run the application in debug mode so that I can use your select for the effective permissions. The Execute permissions are there for the read-only procedures and I'm inheriting them from membership of the domain users group. There are no permissions on the update procedures (and I can't even see them via SSMS when I'm that user). The user definitely has Execute granted on the dbo schema though - I can see that via SSMS. Granting execute on the schema has worked in other databases. Is it because this user is part of domain users and therefore only using those permissions rather than the extra ones it's been given?
Thanks again.
April 1, 2014 at 4:26 am
Chris Wooding (4/1/2014)
Granting execute on the schema has worked in other databases. Is it because this user is part of domain users and therefore only using those permissions rather than the extra ones it's been given?
Not unless there is a revoked or denied permission somewhere along the chain for the domain group. SQL works on the "least permissions" principal, but that principal doesn't automatically deny permissions previously granted. The DBA must specifically revoke or deny those permissions on the group.
The next task is to verify each domain group the user is a part of against the denied sql server permissions.
And did you check my trigger question yet?
April 1, 2014 at 4:32 am
Sorry, yes I did check for triggers and there aren't any (not visible to my login anyway - I'll ask a server admin to check for me).
January 8, 2015 at 2:01 am
Just in case someone else stumbles on this thread, I thought I ought to update it. The problem has been escalated through several layers of Microsoft support to no avail. They are intrigued, but not enough to invest more time unless we pay for it. As it's not a problem in our production environment, we're going to let it lie.
Thanks all for the suggestions so far.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply