Permission on table doesn''t work

Question

Permission on table doesn''t work

halifaxdal

SSCoach

Points: 19741
More actions
June 13, 2006 at 2:59 pm

#61246

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

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

halifaxdal SSCoach Points: 19741 More actions · Answer 2

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?

skeane SSC Eights! Points: 877 More actions · Answer 3

Does the owner of the proc have access to all the tables? Are you using dynamic sql?

halifaxdal SSCoach Points: 19741 More actions · Answer 4

Yes I have a dynamic sql in the sp, is it that causing problem?

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

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.

halifaxdal SSCoach Points: 19741 More actions · Answer 6

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

&nbsp

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