permissions required for executing CDOSys stored procedures

Hi there
I'm doing some analysis on the database applications in my organisation
before migrating the databases to a new server. One of these makes use of
CDOSys objects for sending mail, instead of SQL Mail. There are a number of
stored procedures within the database that call the sp_OACreate &
sp_OASetProperty. Apparently only members of the sysadmin role can execute
these stored procedures, however, the sql login for this application is not
a
sysadmin! I thought perhaps there was a mistake in books online but i've
looked on google and the permission requirements are the same - must be
sysadmin. Any ideas how it still manages to function without these rights?
Thanks in advance!Neile,
Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
SomeOtherPrincipal.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Thanks for the response. I had a look at the permissions on the stored
procedures and could see none granted on the login in question... bizarre.
I
assume the way to check this is by going into the procedure properties scree
n
and clicking on the permissions tab? Is there another view somewhere else'
thx
"Russell Fields" wrote:

> Neile,
> Actually, it is possible to GRANT EXECUTE ON sp_OACreate TO
> SomeOtherPrincipal.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>
>|||Neile,
I usually use sp_helprotect to investigate these things. E.g.
sp_helprotect @.name='sp_OACreate'
I don't know what version of SQL Server you are using, but in 2005 the
properties of the sp_OACreate extended stored procedure has a permissions
page that shows this also, at least in SP2. With SQL Server 2000 Enterprise
Manager there is a permissions button on the properties form that serves a
similar purpose.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...[vbcol=seagreen]
> Thanks for the response. I had a look at the permissions on the stored
> procedures and could see none granted on the login in question...
> bizarre. I
> assume the way to check this is by going into the procedure properties
> screen
> and clicking on the permissions tab? Is there another view somewhere
> else'
> thx
> "Russell Fields" wrote:
>|||Hi Russell,
Thanks for this. It's a sql 2000 box and i had checked the permission
properties page as you suggested below but it was blank. So it appears that
the these stored procedures are being executed by a user who is not a
sysadmin and who has not been granted specific execute permissions on the
procedures.
Is this possible'
Thanks
"Russell Fields" wrote:

> Neile,
> I usually use sp_helprotect to investigate these things. E.g.
> sp_helprotect @.name='sp_OACreate'
> I don't know what version of SQL Server you are using, but in 2005 the
> properties of the sp_OACreate extended stored procedure has a permissions
> page that shows this also, at least in SP2. With SQL Server 2000 Enterpri
se
> Manager there is a permissions button on the properties form that serves a
> similar purpose.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:850ABA77-4CBA-4788-99A4-E11B4530B044@.microsoft.com...
>
>|||Neile,
I don't know what to say about a blank properties page except, "I don't
know." Of course, the sp_OA procedures can be granted to public, which
would give everybody rights, but would also result in public having a green
check box next to it.
What was the response to: sp_helprotect @.name='sp_OACreate'
Another possibility is that the user is also in the local administrators
group of the server. If so, he is by default member of the sysadmin server
role on the SQL Server database unless steps are taken to prevent that. For
example, as described in http://support.microsoft.com/kb/263712/.
RLF
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...[vbcol=seagreen]
> Hi Russell,
> Thanks for this. It's a sql 2000 box and i had checked the permission
> properties page as you suggested below but it was blank. So it appears
> that
> the these stored procedures are being executed by a user who is not a
> sysadmin and who has not been granted specific execute permissions on the
> procedures.
> Is this possible'
> Thanks
> "Russell Fields" wrote:
>|||Oh, and if the user is db_owner in master he also can execute sp_OA
procedures.
The BOL does not say this is possible, but I just tested it and it does work
on SQL 2000 and 2005. (So is this an 'undocumented' feature or an error in
documentation? I have reported it as an error.)
RLF
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:OdHHkzqrHHA.3492@.TK2MSFTNGP02.phx.gbl...
> Neile,
> I don't know what to say about a blank properties page except, "I don't
> know." Of course, the sp_OA procedures can be granted to public, which
> would give everybody rights, but would also result in public having a
> green check box next to it.
> What was the response to: sp_helprotect @.name='sp_OACreate'
> Another possibility is that the user is also in the local administrators
> group of the server. If so, he is by default member of the sysadmin
> server role on the SQL Server database unless steps are taken to prevent
> that. For example, as described in
> http://support.microsoft.com/kb/263712/.
> RLF
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:77B05D1F-5599-441B-89CA-B73940426480@.microsoft.com...
>|||Perhaps the cross-database chaining (a.k.a. db_chaining) database option is
turned on. In that case, execute permissions on indirectly referenced
objects in other databases are not needed as long as the ownership chain is
unbroken. Users need execute permissions on only the user stored procedure.
Note that the user database must be owned by 'sa' to maintain an unbroken
chain to master database objects. It is important that only sysadmin users
be allowed to create dbo-owned objects in this scenario in order to prevent
elevation of privileges.
Hope this helps.
Dan Guzman
SQL Server MVP
"Neile Bermudes" <NB@.community.nospam> wrote in message
news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
> Hi there
> I'm doing some analysis on the database applications in my organisation
> before migrating the databases to a new server. One of these makes use of
> CDOSys objects for sending mail, instead of SQL Mail. There are a number
> of
> stored procedures within the database that call the sp_OACreate &
> sp_OASetProperty. Apparently only members of the sysadmin role can execute
> these stored procedures, however, the sql login for this application is
> not a
> sysadmin! I thought perhaps there was a mistake in books online but i've
> looked on google and the permission requirements are the same - must be
> sysadmin. Any ideas how it still manages to function without these rights?
> Thanks in advance!|||Dan,
Thanks for that reminder. We broke our ownership chains many years ago,
well before SQL 2000 SP3, by giving each database a different owner. It had
faded from memory.
RLF
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
> Perhaps the cross-database chaining (a.k.a. db_chaining) database option
> is turned on. In that case, execute permissions on indirectly referenced
> objects in other databases are not needed as long as the ownership chain
> is unbroken. Users need execute permissions on only the user stored
> procedure.
> Note that the user database must be owned by 'sa' to maintain an unbroken
> chain to master database objects. It is important that only sysadmin
> users be allowed to create dbo-owned objects in this scenario in order to
> prevent elevation of privileges.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Neile Bermudes" <NB@.community.nospam> wrote in message
> news:2048E482-F053-4BFA-92F4-D77C01A2FB54@.microsoft.com...
>|||Hi Russell
Thanks for all the tips - this is all useful stuff to know. I discovered
that the stored procedure in the database which in turn calls the CDOSys
stored procedures is executed via a SQL job - and thus in the context of the
job owner, which is sa. So that explains it!!
But thanks for your suggestions and thanks to Dan for the info about cross
db chaining - all good to know.
Cheers
Neile
"Russell Fields" wrote:

> Dan,
> Thanks for that reminder. We broke our ownership chains many years ago,
> well before SQL 2000 SP3, by giving each database a different owner. It h
ad
> faded from memory.
> RLF
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:48E04539-9C60-4265-94CD-19AEDFCA6443@.microsoft.com...
>
>

permissions needed for executing stored procedures

We want to create a SQL login say user1 and the only privilges we want to
grant it is to be able to execute stored procedures in that database.
So is it just good enough to just do the following ?
Grant exec on sprocx to user1
Does this take care of conditions that include DMLs ( insert,updates,selects
and deletes) that are within the stored procedure ? What about creating temp
tables,etc. ?
Thanks
Yes, doing that is possible, if the owner of the stored procedure (other
than user1 in your example) is also the owner of the tables to do the INSERT,
DELETE, etc.
Take a look at Ownership Chains in BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs ( insert,updates,selects
> and deletes) that are within the stored procedure ? What about creating temp
> tables,etc. ?
> Thanks
>
|||Hassan (hassan@.test.com) writes:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs (
> insert,updates,selects and deletes) that are within the stored procedure?
Yes, provided that the tables and the procedures have the same owner.
And provided that you don't engage in dynamic SQL.
Also beware that if your stored procedures goes beyond INSERT, UPDATE,
DELETE and SELECT, granting execution rights to the procedure is not
sufficient. However, SQL 2005 offers mechanisms that permit you to address
this. I have an article on by web site that discusses this in detail:
http://www.sommarskog.se/grantperm.html

> What about creating temp tables,etc. ?
Any user have the permission to create temp tables, stored procedures or
not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

permissions needed for executing stored procedures

We want to create a SQL login say user1 and the only privilges we want to
grant it is to be able to execute stored procedures in that database.
So is it just good enough to just do the following ?
Grant exec on sprocx to user1
Does this take care of conditions that include DMLs ( insert,updates,selects
and deletes) that are within the stored procedure ? What about creating temp
tables,etc. ?
ThanksYes, doing that is possible, if the owner of the stored procedure (other
than user1 in your example) is also the owner of the tables to do the INSERT,
DELETE, etc.
Take a look at Ownership Chains in BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs ( insert,updates,selects
> and deletes) that are within the stored procedure ? What about creating temp
> tables,etc. ?
> Thanks
>|||Hassan (hassan@.test.com) writes:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs (
> insert,updates,selects and deletes) that are within the stored procedure?
Yes, provided that the tables and the procedures have the same owner.
And provided that you don't engage in dynamic SQL.
Also beware that if your stored procedures goes beyond INSERT, UPDATE,
DELETE and SELECT, granting execution rights to the procedure is not
sufficient. However, SQL 2005 offers mechanisms that permit you to address
this. I have an article on by web site that discusses this in detail:
http://www.sommarskog.se/grantperm.html
> What about creating temp tables,etc. ?
Any user have the permission to create temp tables, stored procedures or
not.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

permissions needed for executing stored procedures

We want to create a SQL login say user1 and the only privilges we want to
grant it is to be able to execute stored procedures in that database.
So is it just good enough to just do the following ?
Grant exec on sprocx to user1
Does this take care of conditions that include DMLs ( insert,updates,selects
and deletes) that are within the stored procedure ? What about creating temp
tables,etc. ?
ThanksYes, doing that is possible, if the owner of the stored procedure (other
than user1 in your example) is also the owner of the tables to do the INSERT
,
DELETE, etc.
Take a look at Ownership Chains in BOL.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Hassan" wrote:

> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs ( insert,updates,selec
ts
> and deletes) that are within the stored procedure ? What about creating te
mp
> tables,etc. ?
> Thanks
>|||Hassan (hassan@.test.com) writes:
> We want to create a SQL login say user1 and the only privilges we want to
> grant it is to be able to execute stored procedures in that database.
> So is it just good enough to just do the following ?
> Grant exec on sprocx to user1
> Does this take care of conditions that include DMLs (
> insert,updates,selects and deletes) that are within the stored procedure?
Yes, provided that the tables and the procedures have the same owner.
And provided that you don't engage in dynamic SQL.
Also beware that if your stored procedures goes beyond INSERT, UPDATE,
DELETE and SELECT, granting execution rights to the procedure is not
sufficient. However, SQL 2005 offers mechanisms that permit you to address
this. I have an article on by web site that discusses this in detail:
http://www.sommarskog.se/grantperm.html

> What about creating temp tables,etc. ?
Any user have the permission to create temp tables, stored procedures or
not.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Permission problems: Need help with CLR credentials

Hi everyone....

I have made a CLR stored procedure which goes to a shared folder on a remote computer. I am having permission problems when executing the stored procedure. (In case anyone is wondering, I am using the .NET SYSTEM.IO class)

I have done the following:

CREATE CREDENTIAL myuser
WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z';

CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3';

ALTER LOGIN sam WITH CREDENTIAL = myuser;

...(other SQL so that SAM can execute the SP)

Now, I login to SQL Server using the new login, and when I try to execute the CLR stored procedure, I get "unknown username or bad password.".

I know 100% for sure that the account in the "MYUSER" credential has access. Is there something else I need to do?

You need to use Impersonation so that your CLR stored proc accesses external resources as your Windows user account, rather than the account that SQL Server is running under.

You can see an example of how to do this here: http://msdn2.microsoft.com/en-us/library/ms131068.aspx

If you are already doing this and it doesn't work, post the relevant clr code you're using.

Steven

Permission Denied executing SP that reads foreign table!

I thought I had a reasonable understanding of how Stored Procedure security
worked, but I've been caught out by something and now I'm a little puzzled.
When a stored procedure is executed the user calling the SP may not have
access to the base tables, but that doesn't matter as the SP runs under the
creators rights and in this case its the dbo who owns the SP. So far so
good.
However we have split our tables across two database (still on the same
server however). We have company data DB's where all requests originate and
to save time and duplication we have a central database holding information
that is common to all company DB's.
When an SP is executed on a company DB that needs to select data from the
central DB, we get a permission denied message.
Why is this? All objects across these databases on the same server are owned
by the same person... the DBO. so why doesn't this work.
In order to resolve this it seems I'm going to have to give select
permissions on certain tables within this central DB and that is what I was
trying to avoid.
Any help on the mechanics of this would be gratefully received
Steve Le Monnier> When a stored procedure is executed the user calling the SP may not have
> access to the base tables, but that doesn't matter as the SP runs under
> the creators rights and in this case its the dbo who owns the SP. So far
> so good.
This statement is not entirely correct; the proc doesn't run under the
creator's (owner) rights. Permissions on objects referenced by the proc are
not checked as long as the objects involved have the same owner. This is
called an unbroken ownership chain.

> Why is this? All objects across these databases on the same server are
> owned by the same person... the DBO. so why doesn't this work.
Cross-database ownership chaining is a little confusing because of
login/user mapping. It is not the name of the object owner that controls
the ownership chain, it is the login associated with the object owner.
Since the login mapping for the 'dbo' user is determined by database
ownership, both databases need to have the same owner in order to provide an
unbroken ownership chain for dbo-owned objects.
If you are using SQL 2000 SP3 or above, you need to also enable the 'db
chaining' database option for the databases involved. This should be done
only when you fully trust those users that have permissions to create
dbo-owned objects. See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Le Monnier" <steve_lemon@.hotmail.com.nospam.com> wrote in message
news:OBrDEOh3FHA.2432@.TK2MSFTNGP10.phx.gbl...
> I thought I had a reasonable understanding of how Stored Procedure
> security worked, but I've been caught out by something and now I'm a
> little puzzled.
> When a stored procedure is executed the user calling the SP may not have
> access to the base tables, but that doesn't matter as the SP runs under
> the creators rights and in this case its the dbo who owns the SP. So far
> so good.
> However we have split our tables across two database (still on the same
> server however). We have company data DB's where all requests originate
> and to save time and duplication we have a central database holding
> information that is common to all company DB's.
> When an SP is executed on a company DB that needs to select data from the
> central DB, we get a permission denied message.
> Why is this? All objects across these databases on the same server are
> owned by the same person... the DBO. so why doesn't this work.
> In order to resolve this it seems I'm going to have to give select
> permissions on certain tables within this central DB and that is what I
> was trying to avoid.
> Any help on the mechanics of this would be gratefully received
> Steve Le Monnier
>
>|||Thanks for the input Dan, I'm going to hit the Books On Line to read up on
DB Chaining.
Cheers
Steve Le Monnier
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eJrlkyh3FHA.3976@.TK2MSFTNGP15.phx.gbl...
> This statement is not entirely correct; the proc doesn't run under the
> creator's (owner) rights. Permissions on objects referenced by the proc
> are not checked as long as the objects involved have the same owner. This
> is called an unbroken ownership chain.
>
> Cross-database ownership chaining is a little confusing because of
> login/user mapping. It is not the name of the object owner that controls
> the ownership chain, it is the login associated with the object owner.
> Since the login mapping for the 'dbo' user is determined by database
> ownership, both databases need to have the same owner in order to provide
> an unbroken ownership chain for dbo-owned objects.
> If you are using SQL 2000 SP3 or above, you need to also enable the 'db
> chaining' database option for the databases involved. This should be done
> only when you fully trust those users that have permissions to create
> dbo-owned objects. See the Books Online for more information.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Steve Le Monnier" <steve_lemon@.hotmail.com.nospam.com> wrote in message
> news:OBrDEOh3FHA.2432@.TK2MSFTNGP10.phx.gbl...
>