October 31, 2014 at 8:04 am
Hi,
I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. Can anyone explain to me what is going wrong here? I see some suggestions that this would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@user-id int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @user-id
execute SQL:
EXEC schemaname.SetUserCulture @user-id = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
October 31, 2014 at 8:43 am
kyagi.jo (10/31/2014)
Hi,I am writing a stored procedure which updates a table, but when I run the stored procedure using a login that I have granted execute privileges on, then I get a message that I cannot run an update on the table. Can anyone explain to me what is going wrong here? I see some suggestions that this would happen in dynamic sql... while my SQL has parameter references, I don't think it is considered dynamic SQL?
sproc:
CREATE PROCEDURE [schemaname].[SetUserCulture]
@user-id int
, @Culture nvarchar(10)
AS
UPDATE dbo.SecUser
SET Culture = @Culture
WHERE UserID = @user-id
execute SQL:
EXEC schemaname.SetUserCulture @user-id = 9, @Culture = N'x'
error:
The UPDATE permission was denied on the object 'SecUser', database 'DatabaseName', schema 'schemaname'.
Who is the owner of [schemaname]?
October 31, 2014 at 8:46 am
owner of the schemaname is dbo
October 31, 2014 at 9:01 am
Huh.. This does seem to be something to do with the schema, I switched the schema to dbo and it managed to work... Not sure what I am meant to set if I have to use a different schema?
October 31, 2014 at 9:29 am
ok it looks like I have to keep this in the same schema, I can't update a table of another schema in a stored procedure not of the same schema. Is this always going to be the case with SQL server, or is there some permission I can give to a login which would give it enough permission to bypass this?
October 31, 2014 at 10:11 am
It is more than just the schema. Tell us more about the user that is calling the procedure.
October 31, 2014 at 10:27 am
Yeah actually that was a false positive, I wasn't actually doing an update as I hadn't populated the test table, that was why it didn't produce an error. Setting the sproc to dbo definitely works though.
The login executing it is just a db_datareader and has a role assigned that I created, to which I assigned execute permission for the stored procedure. schema default is dbo for the login. No schemas owned.
October 31, 2014 at 10:42 am
If [schemaName] is really owned by dbo, then execute permissions on the procedure should work using ownership chaining. Unless there is a specific deny update permission for that user. What does this query return in that database:
SELECT
S.name AS schemaName,
DP.name AS principalName
FROM
sys.schemas AS S
JOIN sys.database_principals AS DP
ON S.principal_id = DP.principal_id
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 31, 2014 at 10:43 am
Weird, I can't seem to use the securables tab of the DB user to assign the stored procedure that was created under this schema to the user, I can add it, but when I press "ok", it forgets it again. Think there is something inherently wrong with this schema, as it is the only one that it happens with
Edit: Ok, I think I may have been looking at the wrong place for the schema owner, it was set to another login, I think this was the cause of most of my problems, as I can run both stored procedures now, irrespective of schemas.
Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply