Re: sql2k5 security


<param@xxxxxxxxxxxxxxxx> wrote in message news:%23wibgafmGHA.4512@xxxxxxxxxxxxxxxxxxxxxxx
OK. So I dropped all the logins roles and re-created them and it is working now. However, I am having a problem with 1 stored procedure. This stored procedure builds a dynamic sql select statement and then executes it using "exec". This stored procedure fails with the permission denied error. Please advise.


By default the user would need permission to run the dynamic query.

Here is an article about your options here

Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html

David

.

Relevant Pages

  • Re: sql2k5 security
    ... So I dropped all the logins roles and re-created them and it is working ... procedure builds a dynamic sql select statement and then executes it using ... This stored procedure fails with the permission denied error. ...
    (microsoft.public.sqlserver.security)
  • 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)