[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Change Default Permissions in SQL Server 2000 SP

Posted on 2007-10-10
4
Medium Priority
?
291 Views
Last Modified: 2010-04-21
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.
0
Comment
Question by:dmoring
  • 2
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20047537
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
 

Author Comment

by:dmoring
ID: 20047552
Thanks, but you missed: "without having to click every SP in the EM GUI "
0
 
LVL 23

Accepted Solution

by:
Ashish Patel earned 2000 total points
ID: 20047666
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
 

Author Closing Comment

by:dmoring
ID: 31408104
Thanks!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question