June 13, 2006 at 2:59 pm
Hi folks,
I am using SQL2000, I have a role DbUser, I have users under this role.
I grant permission to only stored procedures and functions for this role. I didn't grant permission to tables.
Why users can't run stored procedure, error says permission on table is denied.
But, the wierd thing is in the stored procedure, it didn't fail at the first and second select statements(two tables), it fails on the third select statements(another table). There is no difference between the three tabels.
Does anyone have similar problem before? How do I fix this?
Thanks.
June 13, 2006 at 5:09 pm
Do the tables and stored procedures have the same owner? If not, make it so and see if the role members are able to execute stored procedures.
Greg
Greg
June 13, 2006 at 5:15 pm
Hi Greg,
The role has the execution right to sp.
Both table and sp have the same owner: dbo
I tested this way:
run: select * from table in QA, get permissiondeny error, which is correct because I didn't assign permission on table to the user
run sp which includes the above select statement, get the same permission deby error.
But, I already assign execution permission on sp for that user, I assume I should be able to get record.
Why?
June 13, 2006 at 5:28 pm
Does the owner of the proc have access to all the tables? Are you using dynamic sql?
June 13, 2006 at 5:35 pm
Yes I have a dynamic sql in the sp, is it that causing problem?
June 14, 2006 at 8:55 am
Dynamic SQL requires compiling the command, as though you had typed it in QA. So it will only do things the user has the right to do.
If a user has the right to execute a stored procedure, when that stored proc accesses a table with the same owner as the stored proc (presumably dbo), ownership chaining permits the operation without any further permission checking. But short-circuiting the permissions checking (for the compiled statements) doesn't mean the user inherits the dbo role or any dbo privileges, so the dynamic sql is compiled using their standard permissions.
June 14, 2006 at 10:24 am
Thanks for the replying. All the sp and table's own are dbo In sp, I used a dynamic sql select, which I was told sort of messing up the permission. OK, then as told, I grant permission on table1 which I used in my dynamic sql, however, I still don't get result using sp. Here is my code: Code: SELECT @uid = UserID, @joblevel = JobLevel, @did = DepartmentID FROM UserAccount WHERE UserLogon = suser_sname()
Declare @clause Varchar(1000) select @clause = Clause From CursorClause Where joblevel = @Joblevel and MenuNumber = @MenuNumber SET @clause = REPLACE(@clause,'@MenuNumber', @MenuNumber) SET @clause = REPLACE(@clause,'@uid', @uid) SET @clause = REPLACE(@clause,'@did', @did) SET @clause = REPLACE(@clause,'@joblevel', @joblevel) CREATE TABLE #t ( numID INTEGER IDENTITY(1,1), WorkRequestID int, StatusID int  
Declare @sql Varchar(255) SELECT @sql = 'Select distinct WorkRequestID, StatusID From WorkRequest w ' + @clause DECLARE @TabCount INTEGER DECLARE @LoopCount INTEGER insert #t Exec (@sql) Permission setting: Select permission on table [WorkRequest] No select permission on [UserAccount], [CursorClause] Execution permission on sp |
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply