RE: Create Table security

From: Bill Hollinshead [MS] (billhol@online.microsoft.com)
Date: 11/11/02 

From: billhol@online.microsoft.com ("Bill Hollinshead [MS]")
Date: Mon, 11 Nov 2002 19:38:06 GMT

Hi Toby,

While you can grant execute permissions of a stored procedure to users,
there are (in my experience) usually two additional things to consider:
1. Chain of ownership - see
http://msdn.microsoft.com/library/en-us/adminsql/ad_security_4iyb.asp
2. Execution context of dynamic SQL - see the Permissions section of
http://msdn.microsoft.com/library/en-us/tsqlref/ts_ea-ez_05ro.asp

It may be that this stored procedure is creating a table via dynamic SQL,
i.e., EXEC(@dynamicSQLstring)

If the above hasn't helped, please post a script to create the stored
procedure and please post the verbatim error message.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Relevant Pages

  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... stored procedure, the user needs EXECUTE rights to the stored procedure. ... Query 1 requires the user to have SELECT rights on the Customers ... Also, FWIW, the dynamic SQL is only syntax checked at run time, not when the ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Injection Prevention
    ... here is the summary from my posts to that thread that are related to your ... introducing another SQL injection vulnerability. ... of triggers that are using dynamic sql). ... dynamic SQL in stored procedure is much more likely to occur than ...
    (microsoft.public.sqlserver.server)
  • Re: Pass-thru SQL performance vs Stored Proc
    ... >> are going to have to create a lot of dynamic SQL based on user selects ... > stored procedure per permutation of parameters, ... > ms per execution, and you are executing it 100 times an hour, no problem, a ... Right now all our code is in stored procs. ...
    (microsoft.public.sqlserver.programming)
  • Re: stored procedure security
    ... > execute privelages on the stored procedure, ... As soon as you employ dynamic sql, the logon executing the stored procedure ... In order to execute this stored ...
    (microsoft.public.sqlserver.security)
  • Re: DENY table SELECT, GRANT stored proc EXEC, but EXECUTE a string
    ... Once you go to dynamic SQL, you are outside the ownership / security context ... I wrote a script that GRANTS EXECUTE to ... every stored procedure in the database to and DENY ...
    (microsoft.public.sqlserver.security)