Professional Web Applications Themes

Executing packages under a new user - Microsoft SQL / MS SQL Server

Hi, I have built a collection of procedures which act on a number of tables. These work successfully using the "sa" user and now i want to use a new user to execute the packages and modify the data (DML). For my new database i have enabled the db_datareader and db_datawriter options but i cannot work out how to allow the new user to execute any procedure under the new database. I have given specific permissions to the procedure for that user and it now (permission) errors execute a proc in the master db. Can someone explain what is going ...

  1. #1

    Default Executing packages under a new user

    Hi,
    I have built a collection of procedures which act on a number of
    tables. These work successfully using the "sa" user and now i want to
    use a new user to execute the packages and modify the data (DML).
    For my new database i have enabled the db_datareader and db_datawriter
    options but i cannot work out how to allow the new user to execute any
    procedure under the new database.
    I have given specific permissions to the procedure for that user and
    it now (permission) errors execute a proc in the master db.

    Can someone explain what is going on or point me to a resource that
    can :)

    THanks very much.

    Craig
    Culture Guest

  2. #2

    Default Re: Executing packages under a new user

    There is no such thing as a role that allows execution of all stored
    procedures, so you have to GRANT your user EXECUTE permission all of them
    separately.
    The following script will do that for you:

    DECLARE proc_name SYSNAME
    SET proc_name = ''
    WHILE 1=1
    BEGIN
    SET proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
    INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME NOT LIKE 'dt%'
    -- AND ROUTINE_TYPE = 'Procedure'
    AND ROUTINE_NAME > proc_name
    ORDER BY ROUTINE_NAME
    )
    IF proc_name IS NULL BREAK

    EXEC ('GRANT EXECUTE ON ' + proc_name + ' TO MyUser')

    END

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Culture" <com> wrote in message
    news:google.com... 


    Jacco Guest

  3. #3

    Default Re: Executing packages under a new user

    Thank you for the reply and the script!
    I assume that the script would have to be run against my user database
    and master?!?!?

    Craig


    "Jacco Schalkwijk" <co.uk> wrote in message news:<phx.gbl>... [/ref]
    Culture Guest

  4. #4

    Default Re: Executing packages under a new user

    Only against the user database. Whatever a user needs in the master database
    is already covered via the guest user in master, and what is not covered by
    that you usually do not want to give your users access to for security
    reasons. If there are any stored procedures that your user needs in master
    (can't be very many), grant the permission explicitly.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Culture" <com> wrote in message
    news:google.com... 
    news:<phx.gbl>... [/ref]
    them [/ref][/ref]


    Jacco Guest

Similar Threads

  1. [PHP-DEV] RPM packages of PHP 4.3.4
    By Morten Poulsen in forum PHP Development
    Replies: 0
    Last Post: November 4th, 11:17 AM
  2. packages
    By Claus DŁnnebacke in forum AIX
    Replies: 1
    Last Post: September 11th, 08:36 AM
  3. New packages
    By Antony Gelberg in forum Debian
    Replies: 3
    Last Post: July 24th, 11:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139