Change Default Permissions in SQL Server 2000 SP

Posted on 2007-10-10
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.
Question by:dmoring
    LVL 23

    Expert Comment

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

    Author Comment

    Thanks, but you missed: "without having to click every SP in the EM GUI "
    LVL 23

    Accepted Solution

    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.

    Author Closing Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now