SQL Server 2000 SP3 on Win2k Server SP3
Using code: EXEC master..xp_sendmail @.recipients=my_address_goes_here, @.mess
age='',
@.query='SELECT foo FROM bar', @.subject='Subject',
@.attachments='qry.csv',
@.attach_results='TRUE',
@.width=500,@.separator=','
Without @.attachments and @.attach_results the query runs and sends a mail con
taining data. However, it is a user requirement that I send the file as an a
ttached .csv so it will load directly into Excel. With @.attach_results set t
o TRUE and no @.attachments,
the code fails with xp_sendmail: failed with operating system error 267. I'v
e logged in as the SQL Server service account and set its TEMP and TMP envir
onment variables to a folder over which everyone has full control. When I us
e @.attachments, whether I
specify a path or not I get xp_sendmail: failed with operating system error
5.
This looks like a permissions problem, and I've seen several similar posting
s but without a solution. Can anyone point me in the right direction?
Thanks
Scott DoughtyShould also mention the server is clustered
Showing posts with label exec. Show all posts
Showing posts with label exec. Show all posts
Wednesday, March 21, 2012
Permissions problem running xp_sendmail
SQL Server 2000 SP3 on Win2k Server SP
Using code: EXEC master..xp_sendmail @.recipients=my_address_goes_here, @.message=''
@.query='SELECT foo FROM bar', @.subject='Subject'
@.attachments='qry.csv'
@.attach_results='TRUE'
@.width=500,@.separator=',
Without @.attachments and @.attach_results the query runs and sends a mail containing data. However, it is a user requirement that I send the file as an attached .csv so it will load directly into Excel. With @.attach_results set to TRUE and no @.attachments, the code fails with xp_sendmail: failed with operating system error 267. I've logged in as the SQL Server service account and set its TEMP and TMP environment variables to a folder over which everyone has full control. When I use @.attachments, whether I specify a path or not I get xp_sendmail: failed with operating system error 5
This looks like a permissions problem, and I've seen several similar postings but without a solution. Can anyone point me in the right direction
Thank
Scott DoughtShould also mention the server is clustered
Using code: EXEC master..xp_sendmail @.recipients=my_address_goes_here, @.message=''
@.query='SELECT foo FROM bar', @.subject='Subject'
@.attachments='qry.csv'
@.attach_results='TRUE'
@.width=500,@.separator=',
Without @.attachments and @.attach_results the query runs and sends a mail containing data. However, it is a user requirement that I send the file as an attached .csv so it will load directly into Excel. With @.attach_results set to TRUE and no @.attachments, the code fails with xp_sendmail: failed with operating system error 267. I've logged in as the SQL Server service account and set its TEMP and TMP environment variables to a folder over which everyone has full control. When I use @.attachments, whether I specify a path or not I get xp_sendmail: failed with operating system error 5
This looks like a permissions problem, and I've seen several similar postings but without a solution. Can anyone point me in the right direction
Thank
Scott DoughtShould also mention the server is clustered
Permissions on stored procedures
Using Server Management Studio Express and SQL Server 2005 Express - is it possible to assign Exec permissions for users on a sproc by sproc basis. If so, how do I do this?
TIA
--
Mike BrindOk - I've found out how to do it. Just run a GRANT command in the Query Pane on the chosen procedure for the selected user.
sql
Labels:
assign,
basis,
database,
exec,
express,
management,
microsoft,
mysql,
oracle,
permissions,
procedures,
server,
sproc,
sql,
stored,
studio,
users
Permissions on sp_OASetProperty
We have a request to be able to execute the following from an application:
declare @.languageValue varchar(1000)
exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
Currently the developer is unable to because they get an "EXECUTE permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.
We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.
I appreciate any suggestions.
thanks
Meenal> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
Assuming SQL 2000 SP3 or above, you can use cross-database ownership
chaining to allow limited access to the sp_OA* procs and other potentially
dangerous master procs. Note that you should enable 'db chaining' in an
sa-owned database when only sysadmin role members are permitted to create
dbo-owned objects in that database.
1) From Enterprise Manager, uncheck 'only uses with sysadmin...' under
Management-->SQL Server Agent-->Job System and specify a Windows proxy
account when prompted. This proxy account is used as the OS security
context for access to external resources.
2) Change your user database owner to 'sa':
USE MyDatabase
EXEC sp_changedbowner 'sa'
3) Enable the 'db chaining' database option
EXEC sp_dboption 'MyDatabase', 'db chaining', true
4) Create a proc to wrap the needed functionality:
CREATE PROC dbo.usp_DoSomething
@.MyData
AS
--initialization code, including sp_OACreate
EXEC sp_OASetProperty
@.token,
@.languageValue,
'CharSet',
'Shift-JIS'
--other code, including sp_OADestroy
GO
5) Create execute permission to users:
GRANT EXEC ON dbo.usp_DoSomething TO MyRole
This will provide an unbroken ownership chain because all of the objects
involved have the same owner (dbo) and both database owners are owned by the
same login (sa).
It is important that the proc be constructed in such a way as to prevent
ad-hoc execution of unintended commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%23EjS4BkaGHA.3992@.TK2MSFTNGP05.phx.gbl...
> We have a request to be able to execute the following from an application:
> declare @.languageValue varchar(1000)
> exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
> Currently the developer is unable to because they get an "EXECUTE
> permission
> denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
> running this.
> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
> I appreciate any suggestions.
> thanks
> Meenal
>
>
>|||Thanks alot for you suggestion- just a question or 2.
If the database is owned by an nt account and we use mixed mode for SQL
authenticate would I just set the proxy account up using the SQL NT login?
thanks
Meenal
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OBMImNsaGHA.1812@.TK2MSFTNGP04.phx.gbl...
can[vbcol=seagreen]
> Assuming SQL 2000 SP3 or above, you can use cross-database ownership
> chaining to allow limited access to the sp_OA* procs and other potentially
> dangerous master procs. Note that you should enable 'db chaining' in an
> sa-owned database when only sysadmin role members are permitted to create
> dbo-owned objects in that database.
> 1) From Enterprise Manager, uncheck 'only uses with sysadmin...' under
> Management-->SQL Server Agent-->Job System and specify a Windows proxy
> account when prompted. This proxy account is used as the OS security
> context for access to external resources.
> 2) Change your user database owner to 'sa':
> USE MyDatabase
> EXEC sp_changedbowner 'sa'
> 3) Enable the 'db chaining' database option
> EXEC sp_dboption 'MyDatabase', 'db chaining', true
> 4) Create a proc to wrap the needed functionality:
> CREATE PROC dbo.usp_DoSomething
> @.MyData
> AS
> --initialization code, including sp_OACreate
> EXEC sp_OASetProperty
> @.token,
> @.languageValue,
> 'CharSet',
> 'Shift-JIS'
> --other code, including sp_OADestroy
> GO
> 5) Create execute permission to users:
> GRANT EXEC ON dbo.usp_DoSomething TO MyRole
>
> This will provide an unbroken ownership chain because all of the objects
> involved have the same owner (dbo) and both database owners are owned by
the
> same login (sa).
> It is important that the proc be constructed in such a way as to prevent
> ad-hoc execution of unintended commands.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:%23EjS4BkaGHA.3992@.TK2MSFTNGP05.phx.gbl...
application:[vbcol=seagreen]
when[vbcol=seagreen]
can[vbcol=seagreen]
>|||I'm not sure why you ask this question. The proxy account is used as the
external OS security context when non-sysadmin users execute the sp_OA*
procs. That Windows account doesn't even need access to SQL Server.
Setting unless the code invoked via sp_OA* connects to SQL using a trusted
connection.
Unless you specify 'sa' as the user database owner, you'll need to add the
users to the master database and grant execute permissions on the sp_OA*
procs. It is technically possible to simply grant execute permissions to
guest or public but that's a very bad security practice.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eEF4P6UbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Thanks alot for you suggestion- just a question or 2.
> If the database is owned by an nt account and we use mixed mode for SQL
> authenticate would I just set the proxy account up using the SQL NT login?
> thanks
> Meenal
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OBMImNsaGHA.1812@.TK2MSFTNGP04.phx.gbl...
> can
> the
> application:
> when
> can
>
declare @.languageValue varchar(1000)
exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
Currently the developer is unable to because they get an "EXECUTE permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.
We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.
I appreciate any suggestions.
thanks
Meenal> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
Assuming SQL 2000 SP3 or above, you can use cross-database ownership
chaining to allow limited access to the sp_OA* procs and other potentially
dangerous master procs. Note that you should enable 'db chaining' in an
sa-owned database when only sysadmin role members are permitted to create
dbo-owned objects in that database.
1) From Enterprise Manager, uncheck 'only uses with sysadmin...' under
Management-->SQL Server Agent-->Job System and specify a Windows proxy
account when prompted. This proxy account is used as the OS security
context for access to external resources.
2) Change your user database owner to 'sa':
USE MyDatabase
EXEC sp_changedbowner 'sa'
3) Enable the 'db chaining' database option
EXEC sp_dboption 'MyDatabase', 'db chaining', true
4) Create a proc to wrap the needed functionality:
CREATE PROC dbo.usp_DoSomething
@.MyData
AS
--initialization code, including sp_OACreate
EXEC sp_OASetProperty
@.token,
@.languageValue,
'CharSet',
'Shift-JIS'
--other code, including sp_OADestroy
GO
5) Create execute permission to users:
GRANT EXEC ON dbo.usp_DoSomething TO MyRole
This will provide an unbroken ownership chain because all of the objects
involved have the same owner (dbo) and both database owners are owned by the
same login (sa).
It is important that the proc be constructed in such a way as to prevent
ad-hoc execution of unintended commands.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:%23EjS4BkaGHA.3992@.TK2MSFTNGP05.phx.gbl...
> We have a request to be able to execute the following from an application:
> declare @.languageValue varchar(1000)
> exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
> Currently the developer is unable to because they get an "EXECUTE
> permission
> denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
> running this.
> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
> I appreciate any suggestions.
> thanks
> Meenal
>
>
>|||Thanks alot for you suggestion- just a question or 2.
If the database is owned by an nt account and we use mixed mode for SQL
authenticate would I just set the proxy account up using the SQL NT login?
thanks
Meenal
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OBMImNsaGHA.1812@.TK2MSFTNGP04.phx.gbl...
can[vbcol=seagreen]
> Assuming SQL 2000 SP3 or above, you can use cross-database ownership
> chaining to allow limited access to the sp_OA* procs and other potentially
> dangerous master procs. Note that you should enable 'db chaining' in an
> sa-owned database when only sysadmin role members are permitted to create
> dbo-owned objects in that database.
> 1) From Enterprise Manager, uncheck 'only uses with sysadmin...' under
> Management-->SQL Server Agent-->Job System and specify a Windows proxy
> account when prompted. This proxy account is used as the OS security
> context for access to external resources.
> 2) Change your user database owner to 'sa':
> USE MyDatabase
> EXEC sp_changedbowner 'sa'
> 3) Enable the 'db chaining' database option
> EXEC sp_dboption 'MyDatabase', 'db chaining', true
> 4) Create a proc to wrap the needed functionality:
> CREATE PROC dbo.usp_DoSomething
> @.MyData
> AS
> --initialization code, including sp_OACreate
> EXEC sp_OASetProperty
> @.token,
> @.languageValue,
> 'CharSet',
> 'Shift-JIS'
> --other code, including sp_OADestroy
> GO
> 5) Create execute permission to users:
> GRANT EXEC ON dbo.usp_DoSomething TO MyRole
>
> This will provide an unbroken ownership chain because all of the objects
> involved have the same owner (dbo) and both database owners are owned by
the
> same login (sa).
> It is important that the proc be constructed in such a way as to prevent
> ad-hoc execution of unintended commands.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
> news:%23EjS4BkaGHA.3992@.TK2MSFTNGP05.phx.gbl...
application:[vbcol=seagreen]
when[vbcol=seagreen]
can[vbcol=seagreen]
>|||I'm not sure why you ask this question. The proxy account is used as the
external OS security context when non-sysadmin users execute the sp_OA*
procs. That Windows account doesn't even need access to SQL Server.
Setting unless the code invoked via sp_OA* connects to SQL using a trusted
connection.
Unless you specify 'sa' as the user database owner, you'll need to add the
users to the master database and grant execute permissions on the sp_OA*
procs. It is technically possible to simply grant execute permissions to
guest or public but that's a very bad security practice.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eEF4P6UbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> Thanks alot for you suggestion- just a question or 2.
> If the database is owned by an nt account and we use mixed mode for SQL
> authenticate would I just set the proxy account up using the SQL NT login?
> thanks
> Meenal
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OBMImNsaGHA.1812@.TK2MSFTNGP04.phx.gbl...
> can
> the
> application:
> when
> can
>
Monday, March 12, 2012
permissions for sp_OASetProperty
We have a request to be able to execute the following from an application:
declare @.languageValue varchar(1000)
exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
Currently the developer is unable to because they get an "EXECUTE permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.
We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.
I appreciate any suggestions.
thanks
MeenalI answered your question in the security group.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eQW9OItaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> We have a request to be able to execute the following from an application:
> declare @.languageValue varchar(1000)
> exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
> Currently the developer is unable to because they get an "EXECUTE
> permission
> denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
> running this.
> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
> I appreciate any suggestions.
> thanks
> Meenal
>
>
>
>
declare @.languageValue varchar(1000)
exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
Currently the developer is unable to because they get an "EXECUTE permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.
We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.
I appreciate any suggestions.
thanks
MeenalI answered your question in the security group.
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eQW9OItaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> We have a request to be able to execute the following from an application:
> declare @.languageValue varchar(1000)
> exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
> Currently the developer is unable to because they get an "EXECUTE
> permission
> denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
> running this.
> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
> I appreciate any suggestions.
> thanks
> Meenal
>
>
>
>
permissions for sp_OASetProperty
We have a request to be able to execute the following from an application:
declare @.languageValue varchar(1000)
exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
Currently the developer is unable to because they get an "EXECUTE permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.
We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.
I appreciate any suggestions.
thanks
MeenalI answered your question in the security group.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eQW9OItaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> We have a request to be able to execute the following from an application:
> declare @.languageValue varchar(1000)
> exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
> Currently the developer is unable to because they get an "EXECUTE
> permission
> denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
> running this.
> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
> I appreciate any suggestions.
> thanks
> Meenal
>
>
>
>
declare @.languageValue varchar(1000)
exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
Currently the developer is unable to because they get an "EXECUTE permission
denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
running this.
We have no users (other than dbo & guest) in the master database. How can
this be setup with the least amount of permissions being given.
I appreciate any suggestions.
thanks
MeenalI answered your question in the security group.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Meenal Dhody" <meenal_dhody@.hotmail.com> wrote in message
news:eQW9OItaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> We have a request to be able to execute the following from an application:
> declare @.languageValue varchar(1000)
> exec sp_OASetProperty @.languageValue, 'CharSet', 'Shift-JIS'
> Currently the developer is unable to because they get an "EXECUTE
> permission
> denied on object 'sp_OASetProperty', database 'master', owner 'dbo'." when
> running this.
> We have no users (other than dbo & guest) in the master database. How can
> this be setup with the least amount of permissions being given.
> I appreciate any suggestions.
> thanks
> Meenal
>
>
>
>
Wednesday, March 7, 2012
Permission to see stored procs but not change or exec them?
In Server 2000, our developers can see our stored procs but not change them or run them. In 2005, our developers can not see them at all but claim they need to. I am new to 2005 and have not found a way to achieve this. I would like to grant them access to see the procs and read the code but not change it or execute it. Do you know how this can be done?
Thanks!
hi,
Grant "view definition" permission to user.
regards
sufian
Labels:
claim,
database,
developers,
exec,
microsoft,
mysql,
oracle,
permission,
procs,
run,
server,
sql,
stored
Permission sql script
How can i find out which users have exec priviliges to a sproc using TSQL ?You could use sp_helprotect.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O2xFHt6VDHA.208@.tk2msftngp13.phx.gbl...
How can i find out which users have exec priviliges to a sproc using TSQL ?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:O2xFHt6VDHA.208@.tk2msftngp13.phx.gbl...
How can i find out which users have exec priviliges to a sproc using TSQL ?
Labels:
database,
exec,
microsoft,
mysql,
oracle,
permission,
priviliges,
script,
server,
sproc,
sql,
tsql,
users
Saturday, February 25, 2012
Permission problem
Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!
Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!
Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!
Permission problem
Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will need
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in order
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!
Permission problem
Hi all,
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will nee
d
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in orde
r
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!
I have two sp's, spA and spB and two Databases DB1 and DB2.
DB1 has spA and DB2 has spB
In spA I have a sentence exec DB2.dbo.spB
In spB, in one point, I have the sentece delete from DB1.dbo.table1
I have a user who has access to execute in both databases, DB1 and DB2 in
both sp's spA and spB, respectivly
When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
OBJECT TABLE1
I think it is not necesary to give direct permissions to the tabla in order
to delete from the sp, because of the access to execute the sp's
So, I had to grant permissions to DELETE to the login so I can solve the
problem,
Anybody knows about that? Is it wrong my idea?
Thanks in advance!!Permissions will be checked if an SP tries to access an object outside it's
database. So when you access a table in the other database the user will nee
d
permissions on that object (unless you allow database object permission
chaining).
It doesn't matter that the sp is called from an sp in the other database -
it can't give permission on objects in the other db.
"Javier Rosas" wrote:
> Hi all,
> I have two sp's, spA and spB and two Databases DB1 and DB2.
> DB1 has spA and DB2 has spB
> In spA I have a sentence exec DB2.dbo.spB
> In spB, in one point, I have the sentece delete from DB1.dbo.table1
> I have a user who has access to execute in both databases, DB1 and DB2 in
> both sp's spA and spB, respectivly
> When I run spA in DB1 I got the error message DELETE PERMISSION DENIED IN
> OBJECT TABLE1
> I think it is not necesary to give direct permissions to the tabla in orde
r
> to delete from the sp, because of the access to execute the sp's
> So, I had to grant permissions to DELETE to the login so I can solve the
> problem,
> Anybody knows about that? Is it wrong my idea?
> Thanks in advance!!
Subscribe to:
Posts (Atom)