Execute Permissions on 400 SPROCs

Our developers are rolling out an app with 400 new SPROCS. All data access i
s
done through them. I need to give a single user execute permissions on all
400 SPROCS. It would be easiest to just give the user execute permsissions o
n
all stored procs and remove access from the few that don't apply.
Is there a fast way to do this?The preferred way is to create a database User Role and provide execute
permissions to the User Role. And the same applies for creating a user Role
for DENY EXECUTE.
Then as users come and go, they only have to be added to or removed from the
User Role. The 'Best Practice' is to add the something like the following to
each stored procedure script file (You do have them in source
control -right?).
GRANT EXECUTE ON {StoredProcedureName} TO {UserRole}
And if necessary,
DENY EXECUTE ON {StoredProcedureName} TO {DenyUserRole}
Then, when the files are run on any server, the permissions are correct.
There are some stored procedures for which it is probably not a good idea to
provide users EXECUTE permissions. It is much better to explicitly grant
permissions to each stored procedure rather than use 'blanket' permissions
for all objects. I would much rather know that permissions were explicit
provided than accidentally supplied due to 'sloppiness'.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?|||Dan
SELECT 'GRANT EXECUTE ON [' + USER_NAME(uid) + '].[' + name + '] TO
' +
'[UserNameHere]'
FROM sysobjects
WHERE
type = 'P'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(USER_
NAME(uid)) + '.' +
QUOTENAME(name)), 'IsMSShipped') = 0
--Run the output in QA
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:82DEA919-4FB4-4BE1-B495-3FB1722122BB@.microsoft.com...
> Our developers are rolling out an app with 400 new SPROCS. All data access
> is
> done through them. I need to give a single user execute permissions on all
> 400 SPROCS. It would be easiest to just give the user execute permsissions
> on
> all stored procs and remove access from the few that don't apply.
> Is there a fast way to do this?