June 29, 2010 at 11:07 am
In my MSSQL Server 2000:
- There is an user named "USER1"
- There is a database "DB1" and USER1 has public right only on DB1
- In "DB1" there are 2 tables: [dbo].[Table1] and [user1].[Table2]
- There is stored procedures "STORED1":
CREATE PROCEDURE [STORED1] AS
select * from [dbo].[Table1]
GO
- There is stored procedures "STORED2":
CREATE PROCEDURE [STORED2] AS
EXEC ('select * from [dbo].[Table1]')
GO
- There is stored procedures "STORED3":
CREATE PROCEDURE [STORED3] AS
EXEC ('select * from [user1].[Table2]')
GO
- USER1 is grant to execute STORED1, STORED2 and STORED3
When I use SQL Analyzer, DB1 via USER1
- I execute STORED1 --> it's OK
- But I execute STORED2 --> there is error "SELECT permission denied on object 'Table1', database 'DB1', owner 'dbo'"
Why is it???
I want that USER1 can only access [user1].[Table2] via STORED3 and prevent him to access directly [user1].[Table2] (he can't execute SELECT directly). How can I do???
Thanks a lots
June 29, 2010 at 11:16 am
the issue is the dynamic SQL; if that is used in a proc, the calling user needs to have the rights to the underlying object for whatever the proc is doing.
if your procedure is using dynamic SQL, you'd need to use EXECUTE AS in order to allow your end user to use the STORED2 or STORED3, but still prevent him from direct access to the underlying table.
I'm not sure the EXECUTE AS clause was available in SQL 2000...
Lowell
June 30, 2010 at 12:48 am
Thanks for your advice!
Could you help me next question:
USER1 is owner of [user1].[Table2]
How to PREVENT him access (SELECT, INSERT, UPDATE, DELETE) to [user1].[Table2] directly, I want to let him just only can access via Stored Procedures STORED3
That means in SQL Analyzer:
He can: Exec STORED3
But he can't: SELECT/INSERT/UPDATE/DELETE from [user1].[Table2] ......
Thanks again!
July 7, 2010 at 9:00 am
Any body comments please!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply