Re: stored procedure security

From: Tenaya (ct_at_ct.ct)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 16:39:48 -0400

JAverill,

> Is there any way that I can create the dynamic sql statement and still
only have to grant the account
> execute privelages on the stored procedure, and nothing on the tables?

As soon as you employ dynamic sql, the logon executing the stored procedure
must have not only EXEC privileges on the stored procedure but appropriate
privileges on any object referenced within the dynamic SQL.

If you scan (via Google) the archives of this newsgroup, as well as the
.programming newsgroup, you will encounter extensive discussions about the
merits and disadvantages of using dynamic SQL.

Chief Tenaya

"JAverill" <anonymous@discussions.microsoft.com> wrote in message
news:26BFB211-7B11-4A81-8304-A4F0EFF6B25D@microsoft.com...
> I have created a stored procedure that dynamically creates a sql select
statement into a string based on the input variables, then executes the
string using "exec sp_SQLExec @sql". In order to execute this stored
procedure from ASP .NET I had to grant the account that the web server
connects to SQL select privelages on the tables I access in the stored
procedure. Is there any way that I can create the dynamic sql statement and
still only have to grant the account execute privelages on the stored
procedure, and nothing on the tables?
> Thanks!

Relevant Pages

  • Re: Logging within User_Defined Functions (UDF)
    ... You can't perform dynamic SQL or DML in a function. ... Looks like a stored procedure that you ... Ultimately you can only execute ... > DECLARE @cmdstr nvarchar ...
    (microsoft.public.sqlserver.programming)
  • 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)
  • Re: Confused with security
    ... >Using windows integrated security. ... Your guess is correct - welcome to the pitfalls of dynamic SQL. ... If a stored procedure references a table owned by the same userid that ... Each user that has rights to execute the procedure can ...
    (microsoft.public.sqlserver.server)
  • Re: Confused with security
    ... >>Using windows integrated security. ... > Your guess is correct - welcome to the pitfalls of dynamic SQL. ... > owns the stored procedure, no additional check for access right to that ... Each user that has rights to execute the procedure can ...
    (microsoft.public.sqlserver.server)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)