Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-10 : 08:54:21
|
| Rajesh writes "How can I avoid execute statement checking the table level permission ??eg. I got a table create table dbo.test(name varchar(50))and a stored proc -create proc dbo.pr_testasdeclare @strSQL varchar(100)set @strSQL = 'insert into test values(''john'')'exec (@strSQL)suppose the proc pr_test is executed by a COM user who does NOT have permission on any of the tables in the database, the exec statement fails saying insert permission denied on table test.Is there any work around to avoid this error ??thanks,Rajesh" |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-04-10 : 09:13:03
|
quote:
suppose the proc pr_test is executed by a COM user who does NOT have permission on any of the tables in the database, the exec statement fails saying insert permission denied on table test.
The semantics of security settings are sometimes confusing; let me ask a question to make sure I understand you.When you say "doesn't have permission", do you mean that the user has been explicitly denied rights on the table, or rather that the user has not been explicitly granted rights? Security is three-state, not two - explicit grant, explicit deny, or inherited.if this user has been explicitly denied access then you should change the security setting, not worry about the t-sql syntax. if the user can inherit rights to the table through an ownership chain, you should check to make sure the user has execute permission on the stored procedure ( or select on the view, or whatever ).setBasedIsTheTruepath<O> |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-04-10 : 14:22:20
|
| You also might want to look into ways to avoid using dynamic SQL (the exec command). A stored procedure can issue the INSERT statement directly, and will run with the permissions of its creator, whereas dynamic SQL requires the executing user to have SELECT permissions to all tables involved in the dynamic SQL statement.------------------------GENERAL-ly speaking... |
|
|
|
|
|
|
|
|
Avoiding execute statement checking table level permission