February 28, 2002 at 11:06 pm
Is their a Sql 7 pre-defined role (in the spirit of db_datareader/writer) that allows users to execute stored procs in a database?
March 1, 2002 at 4:18 am
As far as I know, no.
However, what we do is create a role in each database and give that role execute permissions to all stored procedures. Then we place the users in the role who need to execute the stored procedures.
Depending on what you are planning to do this could work for you.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
March 1, 2002 at 5:42 am
You could also create the rol in the model database that way when you create a new database it will already be there, then you just have to assign permissions to SPs you create in those databases. And when you add a user you will get a listing of roles that includes your role on Database Access permit options. However any preexisting DBs will not contain the role.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 1, 2002 at 5:54 am
Not sure how much time you save by adding it to the model, but if you do attempt that pattern it would make sure you stayed consistent (or try to anyway). In my experience I rarely grant all users the right to execute all procs. I prefer to grant access based on a business/named role - read only, temps, mastersofall, etc.
Andy
March 1, 2002 at 11:09 am
Tend to agree with Andy. I usually make a role as soon as I create the database for the general access for that database. Might be webusers, might be MyAppUsers, etc. Then I grant rights to each object as I create it, including this security in the script for the object.
Steve Jones
March 3, 2002 at 8:57 pm
Anything other than what Andy or Steve suggests makes me nervous from a security perspective. If you need to retrofit something, you can build your query by doing something akin to the following:
SELECT 'GRANT EXECUTE ON [' + name + '] TO myrole'
FROM sysobjects
WHERE xtype = 'P'
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply