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