Change Default Permissions in SQL Server 2000 SP

I have a db with users granted public and db_datareader roles.  I would like to allow a new/existing role to execute every SP in the db.  What is the procedure to do this without having to click every SP in the EM GUI and without issuing the dbo role.
dmoringAsked:
Who is Participating?
 
Ashish PatelCommented:
Okay in your database just execute this statement. Here xxxxRoleNamexxxx is the role name.

select 'GRANT Execute ON ' + Name + ' TO xxxxRoleNamexxxx' from sysobjects where xtype='P' order by name

Then copy all the rows and just execute them in other window in same database. This should be real easy for you.
0
 
Ashish PatelCommented:
Right click - roles and the click permissions and apply the to execute every sp by clicking the check box on EXEC for all SP's listed in there below.
0
 
dmoringAuthor Commented:
Thanks, but you missed: "without having to click every SP in the EM GUI "
0
 
dmoringAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.