Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Préverenges, Switzerland

    Unanswered: script access to defined sprocs only

    MSSQL-2005 db plus a .NET front-end.

    there are 80 sprocs needed for the application - each sproc-name is LIKE 'abcX_%' or LIKE 'abcY_%'

    there are 3 roles - each role-name is LIKE 'ABC-%' (for example: 'ABC-Reader, 'ABC-User', 'ABC-Superuser')
    priviledges run ABC-Reader ⊂ ABC-User ⊂ ABC-Superuser
    (sprocs LIKE 'abcY_%' are available to all roles LIKE 'ABC-%')

    all are myserver.mydbase.dbo.mysproc/mytable (and yes, i know this is not 2005-think, but my reality is just that)

    ABC-% role-members must ONLY have EXECUTE (some of, according to role) the 80 sprocs LIKE 'abc%' (and connect/tempdb/... etc)
    ...they do not need sp_ or extended sprocs
    ...they MUST NOT have any access to any tables, sp_*, etc. (ignore views for the moment)

    can this be done with
    § one script?
    § three scripts?
    § a script to generate one/three/N scripts

    if it helps to simplify the issue, the sprocs could be renamed 'acp_R%', 'acp_U%' 'acp_S%'

    i don't mind manual stuff, but it takes a lot of mouse-clicks or script-typing to get this done: it should be possible to auto-script it.

    thanks for any suggestions, izy
    Last edited by izyrider; 12-01-09 at 15:04.
    currently using SS 2008R2

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    This is a great example of where schemas (okay, in 2000 they're called owners) would be great.

    Think of schemas as a way of organising your objects in to groups. If you do it this way then granting perms to a single schema is a one line command
    Home | Blog

  3. #3
    Join Date
    Jan 2003
    Provided Answers: 17
    select 'grant execute on [' + name + '] to [group of your choice]'
    from sys.procedures
    where name like 'acp[_]R%'

    EDIT: Fixed a missed quote mark

Posting Permissions

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