Permission
-
I want all my developers to give execute permission on store proc, when user execute store proc its give Update permission denied, I don't want to give permission on tables update.
What should I do??
thanks
Nita
-
Sounds like your developer use dynamic sql within their stored procedures. Have a look at these procedures and tell your developers to avoid dynamic sql for updates.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
No its not helping, the website is in some other language.
-
Hey, nice you visited my site. However, this is my forum signature. My reply is above the signature. Sorry, if this is confusing.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks FrankBTW: The store procedure is not using dynamic query
Nita
-
Can you post the code?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes Sir,
create PROCEDURE dbo.spjobbegin AS
BEGIN
DECLARE @jobId VARCHAR(36),
DECLARE @jobTotal INT
SET @Jobtotal = 0
SELECT @JobTotal = count(*) FROM db_one.dbo.job_tbl
WHERE jobID = @jobID
UPDATE db_one.dbo.job_tbl SET jobTotal=@jobTotal
WHERE jobID = @jobID
IF @jobTotal > 0
INSERT INTO DB_TWO.dbo.job_log (jobID, type, name
VALUES (@jobID, 'True', 'job_tbl')
END
GO
-
I think the issue is the update is to a different database. The cross checking may be enabled, which would cause the permissions to be checked for the second database. Does that give you something to work with?
-
Cross checking????
The db_one and db_two are on same server
-
Yea, actually I knew they were on the same server, or else you would would have had to have had the server name as the first part of the name.
There's some kind of feature called cross-checking or cross-linking or something (we don't use it, I just remember reading about it). You face the choice explicitly when you upgrade, and if you choose it, cross database statements are checked for security.
One easy way to see if you have it is to see if the person running the sp has specific permission to the item in the second db. If not, give it, and see what the result is.
-
He has same execute permission to the other database as well!
I tried pubs and northwind and it works fine ;(
Why the above cannot?
-
Under which security context is the stored procedure executed? It it the same in both db's?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Yes, there is an option that you can only configure when you update sql server 2000. I can't remember if it's call cross chaining database permissions or whatever, but if you didn't install it during the service pack, I think you're out of luck. From what I've been told though, enabling this isn't recommended and can pose security / administration nightmares.
Since your query is accessing another database, you will have to grant update permission to the table in the other database. Your stored procedure security context only exists within the stored procedure's own database.
-
Nita,
Cross-database ownership chaining refers to dependant objects in separate databases being owned by the same login account (not the same database user). It can be set after an installation or upgrade at either the server level or database level.
In your case, assuming the dbo.spjobbegin stored procedure is in db_one and dbo is mapped to the same login in both databases, you can grant EXECUTE on the stored procedure and it will update the table in db_two without you having to grant UPDATE permission on the table.
Greg
Greg
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply