Re: Alter Many Stored Procedures
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 12/15/04
- Next message: Drew: "Re: SQL Permission question"
- Previous message: Uri Dimant: "Re: SQL Permission question"
- In reply to: Vinod Kumar: "Re: Alter Many Stored Procedures"
- Next in thread: SangHunJung: "Re: Alter Many Stored Procedures"
- Reply: SangHunJung: "Re: Alter Many Stored Procedures"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 15 Dec 2004 07:01:23 -0600
Vinod, you are right that db_owner/db_ddladmin role membership provides
permissions to create/drop/alter other objects as well. I also should have
pointed out that it is important to understand the difference between object
permissions (SELECT, INSERT, UPDATE, DELETE, EXECUTE and REFERENCES) and
statement permissions like ALTER. One cannot grant statement permissions to
individual objects. To ALTER an object, one must be one of the following:
1) the object owner
2) a member of the db_ddladmin fixed database role
3) a member of the db_owner fixed database role
4) the database owner
5) a sysadmin server role member
Consequently, if an object is owned by another user like 'dbo', 2-5 above
applies. If those permissions are too powerful, one method I've seen used
is to grant CREATE permissions to the user/group/role so that objects owned
by the user can be modified and then have the DBA use sp_changeobjectowner
to promote to another schema as needed.
-- Hope this helps. Dan Guzman SQL Server MVP "Vinod Kumar" <vinodk_sct@NO_SPAM_hotmail.com> wrote in message news:cpojd4$rqv$1@news01.intel.com... > Having said that db_owner and db_ddladmin also opens doors to do other ddl > operations on the DB. So be careful in the usage. If you would like to > give > specific grants then its a pain staking job of GRANT-ing each SP to the > user > account/distribution list. > > -- > HTH, > Vinod Kumar > MCSE, DBA, MCAD, MCSD > http://www.extremeexperts.com > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:Oajgzek4EHA.1260@TK2MSFTNGP12.phx.gbl... >> The group needs to be a member of the db_owner or db_ddladmin fixed > database >> roles. >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> >> "SangHunJung" <SangHunJung@discussions.microsoft.com> wrote in message >> news:7721EA2E-43D5-49DD-BB58-287F05D77284@microsoft.com... >> > Hello, We have SQL2000 (Standard Edition) server running on Win2k > server. >> > What permission do I need to setup for a developer in order to change > many >> > stored procedures in ABC database? Developer windows group is added to >> > the >> > database and grant all permissions (select, insert, delete, update) to > the >> > group. >> > >> > Thanks in advance >> > SangHun >> >> > >
- Next message: Drew: "Re: SQL Permission question"
- Previous message: Uri Dimant: "Re: SQL Permission question"
- In reply to: Vinod Kumar: "Re: Alter Many Stored Procedures"
- Next in thread: SangHunJung: "Re: Alter Many Stored Procedures"
- Reply: SangHunJung: "Re: Alter Many Stored Procedures"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
