"Henri" <hmfireball@hotmail.com> wrote in message
news:emMHCtVOFHA.3988@tk2msftngp13.phx.gbl...
> Hi,
>
> My DB is called "mydb".
> I've declared a login/user called "mydb_user" who can only run mydb's
> stored
> procedures.
> I've written a stored procedure called "mysp".
> Inside mysp, I execute master's stored procedures: sp_OACreate,
> sp_OAMethod
> and sp_OADestroy.
>
> As mydb_user doesn't have rights to execute master's SPs, I get an error
> when I try to execute mysp.
> I could grant mydb_user to execute sp_OACreate, sp_OAMethod and
> sp_OADestroy
> but I think it would be good for SQLServer security.
>
> Is there a way to grant mydb_user only the right to execute mysp, but to
> grant mysp the right to execute sp_OACreate, sp_OAMethod and sp_OADestroy?
>
> Thanks for your help.
>
> Henri
>
>
>

"Henri" <hmfireball@hotmail.com> wrote in message
news:OOCNUgXOFHA.2348@tk2msftngp13.phx.gbl...
> Yes, but isn't there a way to "impersonate" the login inside the stored
> procedure so that as long as the caller is allowed to execute the stored
> procedure, all the commands inside the procedure are executed as if the
> caller were a sysadmin?
> The caller could then execute sp_OACreate, but only through the stored
> procedure.
>
>
>
> "oj" <nospam_ojngo@home.com> a écrit dans le message de
> news:u59n2SXOFHA.1096@tk2msftngp13.phx.gbl...
>> You cannot grant a db login execute on sp_oacreate without making this
> login
>> a sysadmin.
>>
>> http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp
>>
>> Permissions
>> Only members of the sysadmin fixed server role can execute sp_OACreate.
>>
>>
>> --
>> -oj
>>
>>
>> "Henri" <hmfireball@hotmail.com> wrote in message
>> news:emMHCtVOFHA.3988@tk2msftngp13.phx.gbl...
>> > Hi,
>> >
>> > My DB is called "mydb".
>> > I've declared a login/user called "mydb_user" who can only run mydb's
>> > stored
>> > procedures.
>> > I've written a stored procedure called "mysp".
>> > Inside mysp, I execute master's stored procedures: sp_OACreate,
>> > sp_OAMethod
>> > and sp_OADestroy.
>> >
>> > As mydb_user doesn't have rights to execute master's SPs, I get an
>> > error
>> > when I try to execute mysp.
>> > I could grant mydb_user to execute sp_OACreate, sp_OAMethod and
>> > sp_OADestroy
>> > but I think it would be good for SQLServer security.
>> >
>> > Is there a way to grant mydb_user only the right to execute mysp, but
>> > to
>> > grant mysp the right to execute sp_OACreate, sp_OAMethod and
> sp_OADestroy?
>> >
>> > Thanks for your help.
>> >
>> > Henri
>> >
>> >
>> >
>>
>>
>>
>
>
>

> You cannot grant a db login execute on sp_oacreate without making this
login
> a sysadmin.
>
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp
>
> Permissions
> Only members of the sysadmin fixed server role can execute sp_OACreate.
>
>
> --
> -oj
>
>
> "Henri" <hmfireball@hotmail.com> wrote in message
> news:emMHCtVOFHA.3988@tk2msftngp13.phx.gbl...
> > Hi,
> >
> > My DB is called "mydb".
> > I've declared a login/user called "mydb_user" who can only run mydb's
> > stored
> > procedures.
> > I've written a stored procedure called "mysp".
> > Inside mysp, I execute master's stored procedures: sp_OACreate,
> > sp_OAMethod
> > and sp_OADestroy.
> >
> > As mydb_user doesn't have rights to execute master's SPs, I get an error
> > when I try to execute mysp.
> > I could grant mydb_user to execute sp_OACreate, sp_OAMethod and
> > sp_OADestroy
> > but I think it would be good for SQLServer security.
> >
> > Is there a way to grant mydb_user only the right to execute mysp, but to
> > grant mysp the right to execute sp_OACreate, sp_OAMethod and
sp_OADestroy?
> >
> > Thanks for your help.
> >
> > Henri
> >
> >
> >
>
>
>

> Sql2k does not have a concept of [execute as]. You'll have to wait for
> yukon.
>
> --
> -oj
>
>
> "Henri" <hmfireball@hotmail.com> wrote in message
> news:OOCNUgXOFHA.2348@tk2msftngp13.phx.gbl...
> > Yes, but isn't there a way to "impersonate" the login inside the stored
> > procedure so that as long as the caller is allowed to execute the stored
> > procedure, all the commands inside the procedure are executed as if the
> > caller were a sysadmin?
> > The caller could then execute sp_OACreate, but only through the stored
> > procedure.
> >
> >
> >
> > "oj" <nospam_ojngo@home.com> a écrit dans le message de
> > news:u59n2SXOFHA.1096@tk2msftngp13.phx.gbl...
> >> You cannot grant a db login execute on sp_oacreate without making this
> > login
> >> a sysadmin.
> >>
> >> http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_oa-oz_9k2t.asp
> >>
> >> Permissions
> >> Only members of the sysadmin fixed server role can execute sp_OACreate.
> >>
> >>
> >> --
> >> -oj
> >>
> >>
> >> "Henri" <hmfireball@hotmail.com> wrote in message
> >> news:emMHCtVOFHA.3988@tk2msftngp13.phx.gbl...
> >> > Hi,
> >> >
> >> > My DB is called "mydb".
> >> > I've declared a login/user called "mydb_user" who can only run mydb's
> >> > stored
> >> > procedures.
> >> > I've written a stored procedure called "mysp".
> >> > Inside mysp, I execute master's stored procedures: sp_OACreate,
> >> > sp_OAMethod
> >> > and sp_OADestroy.
> >> >
> >> > As mydb_user doesn't have rights to execute master's SPs, I get an
> >> > error
> >> > when I try to execute mysp.
> >> > I could grant mydb_user to execute sp_OACreate, sp_OAMethod and
> >> > sp_OADestroy
> >> > but I think it would be good for SQLServer security.
> >> >
> >> > Is there a way to grant mydb_user only the right to execute mysp, but
> >> > to
> >> > grant mysp the right to execute sp_OACreate, sp_OAMethod and
> > sp_OADestroy?
> >> >
> >> > Thanks for your help.
> >> >
> >> > Henri
> >> >
> >> >
> >> >
> >>
> >>
> >>
> >
> >
> >
>
>
>