hi all, i have database with lot of stored procedure. after i create a user i usually need to give this user to execute all stored proc in selected database. So i give execute permission to user with selecting proceures one by one. is it possible to give a user execute permission on all stored procs or other objects? thanks in advanced
Generally speaking, you should create database roles, and assign sets of permissions to the database roles. Then with new users, you just make them member of the appropriate role(s).
Something like the following script will give you an execute statement , you can then pick and choose which procedures to allow execute rights select 'Grant Execute on ' + name + ' myUSERName' from sysobjects where xtype in ('P')