Re: sa privileges and roles
From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/15/04
- Next message: Tom Moreau: "Re: Clustered index and primary keys - Help!!"
- Previous message: Dan Guzman: "Re: Changing a primary key data type"
- In reply to: Pranav: "sa privileges and roles"
- Next in thread: Bonj: "Re: sa privileges and roles"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 15 Oct 2004 07:52:47 -0500
You can grant direct permissions to sp_OA* procs to non-sysadmin users but
you need to consider the security implications. The OS security context
will be the SQL Agent proxy account and users will be limited only by the
permissions of that account. The is nothing that will prevent users with
execute permissions to run ad-hoc commands.
Another option is to create a dbo-owned user proc in an sa-owned user
database that executes sp_OA* with only your needed application
functionality. The proc should be coded in such a way to prevent injection
of ad-hoc commands. You can then enable cross-database ownership chaining
in your user database and grant execute permissions only on your user proc.
Role members will be restricted to the functionality of the user procedure.
Note that you should enable cross-database chaining in an sa-owned database
when only sysadmin role members have permissions to create dbo-owned objects
in that database.
-- Hope this helps. Dan Guzman SQL Server MVP "Pranav" <pranavr@hotmail.com> wrote in message news:e2c10da5.0410141956.1f4e776c@posting.google.com... > Hi All, > We have a security requirement that the user account used to connect > to sql should not have sysadmin priv. Now, we peform operations in SQL > that make use of things like > sp_OACreate/sp_replicationdboption/sp_dropdevice etc. > BOL says > "Only members of the sysadmin fixed server role can execute > sp_OACreate." > > What I want to do is create a role, give this role explicit execute > permission on the specific sp_* and add my user account to this role > (and revoke the sa privileges). > Do you think this approach is ok? The concern raised was that the doc > explicity asks for members of sysadmin. This is different from a > statement like > > "Execute permissions default to members of the sysadmin and diskadmin > fixed server roles." > > Thanks > Pranav
- Next message: Tom Moreau: "Re: Clustered index and primary keys - Help!!"
- Previous message: Dan Guzman: "Re: Changing a primary key data type"
- In reply to: Pranav: "sa privileges and roles"
- Next in thread: Bonj: "Re: sa privileges and roles"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
