Re: Preventing unauthorized access

From: Adam Machanic (amachanic_at_air-worldwide.nospamallowed.com)
Date: 11/17/03 

Date: Mon, 17 Nov 2003 15:21:52 -0500

1) / 3) - You will set up multiple users in your database. One of the users
will have full access (this will be your user), the other(s) will have the
restricted access.

2) If you deny permission on an object, e.g. a table, there is no way for
that user to directly select from it. However, if that user has access to a
stored procedure that selects from the table, the user can run the stored
procedure to get the data. That stored procedure cannot use dynamic SQL to
select the data; this causes security to be re-checked at runtime, and as
the user has no access directly to the table, the query will not run.

"Sydney Lotterby" <sydney@infosearch.com> wrote in message
news:#Sjd6PSrDHA.2772@TK2MSFTNGP12.phx.gbl...
> Thanks, but ...
> 1) I need to be able to access the db with EM/QA. Can that still be done
> via a password?
> 2) Does the Dynamic sql restriction only apply to SPs using dynamic or to
> ANY dynamic sql via the application? ( I used ADO)
> 3) Is there not a way to just password protect like there is in MS Access
> so that anyone (including me) could get to the db if they had the
password?

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: 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)
  • 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.dotnet.security)
Quantcast