Post reply

Schema's

  • November 16, 2007 at 6:52 am

    #179348

    I executed a job by writing a direct TSQL Script in the job step .....

    Later i modified it to execute a stored proc ... which now contains my TSQL code. Now the job looks like

    "exec dbo. "

    I also observed there were a few jobs ... which executed a stored proc like " Exec " .....

    Is this is a better approach of executing a stored proc ??

    I am not particularly aware of use of Schema's could some one direct me to the right place?

    but then my code also executed as a job ... Am I lucky that the user under whose account job executes has execute rights ... Or will it be the case always ???

    Thanks .... 🙂 ....

  • November 16, 2007 at 7:29 am

    #751837

    There's quite a bit of information about schemas in the SQL Server 2005 Books Online (documentation that comes with SQL Server). If, however, you're familiar with the concept of namespaces, schema are similar to them. They are used to group like objects together. In addition, they are a security container.

    With respect to executing the job, yes, you need to make sure the owner of the job has the appropriate execute rights. Probably you are executing the job with a member of the sysadmin fixed server role, which has complete rights over the database. If possible, you will want to create proxy accounts and use them, limited the proxy account only to the rights it absolutely must have (such as EXECUTE on that stored procedure).

    K. Brian Kelley
    @kbriankelley

  • November 16, 2007 at 10:33 am

    #751913

    Very rightly said ... the user under which the sql job executes is a member of sysadmin role ....

    so would u recommend creating a proxy account ..... and running SQL job under the proxy account

    or would you recommend creating schema ... and then executing the stored procedure under that schema name .......

    I want to go ahead with a solution which would I can incorporate in a sql script and would work well as a part of deployment .... as in no rework required

  • November 16, 2007 at 11:18 am

    #751939

    Use a proxy account with only the rights necessary to complete the job. The schema doesn't really help you in this context.

    K. Brian Kelley
    @kbriankelley

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply