Accessing data between two instances of SQL Server

If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
execute a query on SERVER_A referencing a table on SERVER_B is that possible?
Example
First Server: SERVER_A, Database: Nuts, Table: Goodness
Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
where I need to get some data from SERVER_A , database 'Nuts', table
'Goodness'.
Is that possible? How can I do it? Would I use a linked server? If so can
anyone show me a good resource on how to set up a linked server?Lookup Linked servers in BOL
you can use sp_addlinkedserver or EM
If this is a 1 time you can use OPENDATASOURCE or OPENROWSET
http://sqlservercode.blogspot.com/
"Joe" wrote:
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want to
> execute a query on SERVER_A referencing a table on SERVER_B is that possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Joe,
1. Create the proc on SERVER_A
2. Create a linked server from B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
3. Create a linked server login for B to A -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
HTH
Jerry
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> to
> execute a query on SERVER_A referencing a table on SERVER_B is that
> possible?
> Example
> First Server: SERVER_A, Database: Nuts, Table: Goodness
> Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> where I need to get some data from SERVER_A , database 'Nuts', table
> 'Goodness'.
> Is that possible? How can I do it? Would I use a linked server? If so can
> anyone show me a good resource on how to set up a linked server?|||Hey thanks - its working great
"Jerry Spivey" wrote:
> Joe,
> 1. Create the proc on SERVER_A
> 2. Create a linked server from B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_8gqa.asp
> 3. Create a linked server login for B to A -
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_adda_6e26.asp
> 4. Run the proc on SERVER_B - EXEC SERVER_A.NUTS.DBO.CHOC_SP 'GOODNESS'
> HTH
> Jerry
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:E35559B3-5FDF-4D98-BA08-69F214538A19@.microsoft.com...
> > If I have two SQL Servers registered say SERVER_A and SERVER_B and I want
> > to
> > execute a query on SERVER_A referencing a table on SERVER_B is that
> > possible?
> >
> > Example
> >
> > First Server: SERVER_A, Database: Nuts, Table: Goodness
> >
> > Second Server: SERVER_B, Database : Oats, Stored Procedure: choc_sp
> >
> > From server SERVER_B database 'Oats' I have a stored procedure 'choc_sp'
> > where I need to get some data from SERVER_A , database 'Nuts', table
> > 'Goodness'.
> >
> > Is that possible? How can I do it? Would I use a linked server? If so can
> > anyone show me a good resource on how to set up a linked server?
>
>

Accessing a DSN through a Sql job

I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.

Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.

Thanks in advance for any assistance you can provide!

Craig

This seems to be security issue, as your package can execute fine within the IDE, but not through the SQL Agent. I'd check the SQL Agent user account, along with the proxy details on the job.

|||

Thank you for your reply, Deniz.

Do you know of any web sites that I can get more information on setting up the Sql Agent account permissions? I granted that user admin privileges, but still receive the same error.

Craig Browder

craigster1976@.msn.com

Access to the remote server is denied because the current security context is not trusted.

Hello,

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.

The remote database is set to Trustworthy.

I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.

I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.

Access to the remote server is denied because the current security context is not trusted.

Hello,

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.

The remote database is set to Trustworthy.

I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.

I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.

Access to the remote server is denied because the current security context is not trusted.

Hello,

In SQL 2005, from a stored procedure in a local database I am attempting to execute a remote stored procedure in another database on another server. I am getting the error referred to in the Subject when the local stored procedure tries to execute the remote stored procedure. A couple of comments:

The remote database is set up as a linked server in the local database. As part of the linked server definition I selected the 'be made using this security context', and provided a local user name and password.

The remote database is set to Trustworthy.

I have tried every combination of WITH Execute As on the remote stored procedure but nothing works.

I can query against the remote database successfully within Management Studio. I can even execute the remote stored procedure successfully from within M.S., but not from within my local stored procedure when it is run.

Thank you for your help on this - Amos.

I assume you are using a Windows principal for the EXECUTE AS statement, correct? If that is the case, I think I know the problem. When using EXECUTE AS <windows_principal> there is no real authentication for the Windows user:

* If you have a OS older than Windows 2003, the Windows token would really be valid for SQL Server and not a real Windows token

* if you are using Windows 2003, and Kerberos is available, the system should use a S4USelf token and these type of tokens are, as far as I understand these tokens are restricted, and out of the box you should not be able to use them on another machine.

If your scenario falls under the S4USelf token, it may be possible to use delegation and use this token on the remote server (during the remote SP call), but I would personally not recommend it.I would prefer to suggest changing the EXECUTE AS clause to use a SQL principal (SQL authentication should work).

Remember that for remote calls to work with EXECUTE AS, it is necessary to trust the impersonated token on the server, turning on the TRUSTWORTHY bit on the source DB (the DB where the local SP resides) and making sure the DBO has AUTHENTICATE SERVER permission (if DBO is a member of sysadmin, this permission is implicitly granted).

Let us know if this information was of any help or if you have additional questions.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

Thank you for taking the time to answer. I am getting a bit desperate for an answer!

First let me say that I don't have a great deal of experience in this area (security). Therefore, I might be asking some pretty dumb questions. My first question is this. On my remote stored proc, do I have to use 'WITH EXECUTE AS'? I would rather not if I don't have to. I cannot get this to work whether I use it or not. So, let's start there. Can I get this to work with the Execute As?

Amos.

|||

You don’t have to use EXECUTE AS on the remote SP unless you want to. I am assuming you want to execute always under the exactly same principal (on the local DB) and that’s why you used execute as on the local SP, but as you will always connect as the same principal on the remote machine, using EXECUTE AS will be of little value.Even for the local SP, you don’t need EXECUTE AS unless you want to always use the same principal (i.e. use it as a proxy) to connect to the remote machine and execute the remote SP.

If you prefer, you can describe the problem you want to solve and I will do my best to help you find a solution.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Raul,

You are giving me too much credit :). I only used Execute As to try to get this to work. So, let's go under the assumption I don't need it. Here is my current setup now:

The remote stored proc does not have 'Execute As'|||

I can see one mistake here: the trustworthy bit is enabled on the remote server, but it should be for the local server. Also make sure that if the DBO for the local SP DB is not a member of sysadmin, to grant AUTHENTICATE SERVER to the DBO login.

The TW bit + AUTEHNTICATE SERVER will tell the local server that the impersonated context is valid across the SQL Server instance, and only then it can be used in remote calls.

BTW. What is the impersonated context (EXECUTE AS clause) being used? Is it a Windows principal or a SQL principal?

|||

Raul,

I don't know how to thank you! That was it. My local database was not set to Trustworthy. I actually thought about changing this yesterday but it didn't make any sense to me to do that so I didn't try it.

Amos.

|||

No problem, I am glad I was able to help you resolve this problem.

Please let us know if you have any further questions or feedback.

-Raul Garcia

SDE/T

SQL Server Engine

|||

For additional information on the TRUSTWORTHY bit, see the following whitepaper:

http://msdn2.microsoft.com/en-us/library/ms188304.aspx

Thanks
Laurentiu

|||

Hi,

Great post, only it doesn't fix the problem that I am having. All the criteria above is true to my situation and I have run through the checklist described, but I still receve the "Access to the remote server is denied because the current security context is not trusted" error message.

Is there anything else that I can try?

Thanks

MIke

|||

thats got it.

The Authenticator of the trust is the DBO of the database, if the DBO is not a member of the target db (and trusted) then the error occurs.

By updating the source DBO to one that is trusted I now have working links.

Thanks for all your help peeps.

Mike

|||

Can you explain the solution in more detail?

I am having a similar problem:

I have a Linked server on a SQL 2005 database that connects to an Ingres DB

I am able to run stored procedures manually in management studio but when I schedule a job to run these procedures I get a similar error - Security context is not trusted.

I've set the local database to have Trustworthy bit on. But how can I set the remote server via the linked server object with the Trustworthy bit. Also I have the Security Context on the linked server - with the option - Connections be made with the following security context - and I have provided a username and password that has access to the remote database.

Thanks

Sg

|||

IIRC, I had to set both the local database and the remote database to Trustworthy. In addition, make sure the "RPC" and "RPC Out" options on the Linked Server defintion are set to True.

Amos.

Access to execute Store procedures

Hello there
I would like to give my users permission to insert/update/delete data
from/to tables and views
and i also need to give them full access to execute Store Procedures.
In order to do so i gave them on Dababase mode db_datareader and
db_datawriter roles.
This roles are giving them full access to insert/update and delete data, but
it doesn't give them access to execute Store procedures.
Which permission i sould give them wituout give them db_owner?Roy
GRANT EXECUTE ON storeprocedure TO username
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
> Hello there
> I would like to give my users permission to insert/update/delete data
> from/to tables and views
> and i also need to give them full access to execute Store Procedures.
> In order to do so i gave them on Dababase mode db_datareader and
> db_datawriter roles.
> This roles are giving them full access to insert/update and delete data,
> but it doesn't give them access to execute Store procedures.
> Which permission i sould give them wituout give them db_owner?
>|||Shalom Uri
This i've already know
I'm looking for role for this and not go spesific on each store procedure.
on the business i'm working on they are afraid to give the users db_owner's
permission which i know that it is not a problem.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
> Roy
> GRANT EXECUTE ON storeprocedure TO username
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:eNhG2daLHHA.4376@.TK2MSFTNGP03.phx.gbl...
>|||Roy
Ok, so create a new role, add to the role users that you want to execute
sp. Go to the permission tab and click/check EXEC column for stored
procedure
Also , you can write script to grant an execute permission for all stored
procedure tospecific user. If you are interested I will post it out
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
> Shalom Uri
> This i've already know
> I'm looking for role for this and not go spesific on each store procedure.
> on the business i'm working on they are afraid to give the users
> db_owner's permission which i know that it is not a problem.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O%23XjUuaLHHA.1280@.TK2MSFTNGP04.phx.gbl...
>|||Roy
Be aware that if you have dymanic sql within a stored procedure you will
have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
as well
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
> Roy
> Ok, so create a new role, add to the role users that you want to execute
> sp. Go to the permission tab and click/check EXEC column for stored
> procedure
> Also , you can write script to grant an execute permission for all stored
> procedure tospecific user. If you are interested I will post it out
>
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uW%23ObyaLHHA.4244@.TK2MSFTNGP04.phx.gbl...
>|||Whell Uri
For this i have already gave db_datareader and db_datawriter role which
supply this need.
In fact before i deal there, it was as you said.
Now after i add db_datareader and db_datawriter this problem has gone and
there is full permission
it is seems to be some stupide of microsoft not to have role to execute
store procedures. it looks like the is reason for it. i'm wondering why
thanks
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23ed005aLHHA.3268@.TK2MSFTNGP04.phx.gbl...
> Roy
> Be aware that if you have dymanic sql within a stored procedure you will
> have to grant SELECT/UPDATE/INSERT/DELETE permission on underlaying table
> as well
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:u$6IH4aLHHA.2456@.TK2MSFTNGP06.phx.gbl...
>|||Roy Goldhammer (roy@.hotmail.com) writes:
> For this i have already gave db_datareader and db_datawriter role which
> supply this need.
> In fact before i deal there, it was as you said.
> Now after i add db_datareader and db_datawriter this problem has gone and
> there is full permission
> it is seems to be some stupide of microsoft not to have role to execute
> store procedures. it looks like the is reason for it. i'm wondering why
You can't do this in SQL 2000, as far as I know. But in SQL 2005,
permissions cascade, and you can say things like:
grant execute on database::yourdb to somerole
grant execute on schema::dbo to someotherrole
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

Access Script Task

Hi,

How to access the Package inside the Script Taks. for example

IF intStatus = 1 Then

//Execute Package1

Else

//Execute Package2

End IF

in the above sample what is the property to get the Package1 object in script

Thanks & Regards

Jegan.T

You should use the Execute Package Task in order to execute other packages. Any reason why this doesn't work for you?

-Jamie

|||

If i use Execute Package i have to supply all the connection string details.we have the requirement is like this we have to excute the package based on a routine or function execution .its more like using batch file in Data Stage.

Jegan.T

|||

Sorry, I don't know anything about DataStage.

The connection string for the package tells you where it is located. Surely you need the same information in order to execute from the Script Task?

-Jamie

|||

Hi Jamie

Thanks for your reply . but the Script Task does not have any provison for connection string .

i have the design like this in the control flow

Script Task -> DataFlow1 --> DataFlow2

we have to write a routine in the script editor which will decied which DataFlow it has to execute

Script Editor

--

Public Sub Main()

'Code to Invoke DataFlow1 or DataFlow2
End Sub

How to achive this in the script editor ?

Thanks

Jegan.T

|||

I'm confused. At the start of this thread you said you were trying to execute a package.

NOW you say you are trying to decide which data-flow to execute.

The two are completely different so which is it? Perhaps you can explain exactly what it is you are trying to do.

If what you are trying to do is conditionally execute a data-flow then you are going about it the wrong way. You do not need to use a script task - use conditional precedence constraits which are described here: http://www.sqlis.com/default.aspx?306

-Jamie

Access right for Database Role

Hi,
For database users assigned with database role "db_datareader" and "Public".
Does it mean that he / she is able to browse all tables and execute Stored
Procedures. OR He / She can only execute Stored Procedures ?
If we want to give him / her access right to exec Stored Procedures only,
which database role should be assigned to him / her ?
Thanks
the db_datareader role only allows the user to read tables in the database,
not exec stored procedures. There is no role that grants exec permissions on
all stored procs inside a db. You will have to manually grant them. See if
this helps:
http://vyaskn.tripod.com/generate_sc..._sql_tasks.htm
I have an example at the above link.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:ORyFIPKTFHA.3056@.TK2MSFTNGP14.phx.gbl...
Hi,
For database users assigned with database role "db_datareader" and "Public".
Does it mean that he / she is able to browse all tables and execute Stored
Procedures. OR He / She can only execute Stored Procedures ?
If we want to give him / her access right to exec Stored Procedures only,
which database role should be assigned to him / her ?
Thanks

Access right for Database Role

Hi,
For database users assigned with database role "db_datareader" and "Public".
Does it mean that he / she is able to browse all tables and execute Stored
Procedures. OR He / She can only execute Stored Procedures ?
If we want to give him / her access right to exec Stored Procedures only,
which database role should be assigned to him / her ?
Thanksthe db_datareader role only allows the user to read tables in the database,
not exec stored procedures. There is no role that grants exec permissions on
all stored procs inside a db. You will have to manually grant them. See if
this helps:
http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm
I have an example at the above link.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:ORyFIPKTFHA.3056@.TK2MSFTNGP14.phx.gbl...
Hi,
For database users assigned with database role "db_datareader" and "Public".
Does it mean that he / she is able to browse all tables and execute Stored
Procedures. OR He / She can only execute Stored Procedures ?
If we want to give him / her access right to exec Stored Procedures only,
which database role should be assigned to him / her ?
Thanks

Access right for Database Role

Hi,
For database users assigned with database role "db_datareader" and "Public".
Does it mean that he / she is able to browse all tables and execute Stored
Procedures. OR He / She can only execute Stored Procedures ?
If we want to give him / her access right to exec Stored Procedures only,
which database role should be assigned to him / her ?
Thanksthe db_datareader role only allows the user to read tables in the database,
not exec stored procedures. There is no role that grants exec permissions on
all stored procs inside a db. You will have to manually grant them. See if
this helps:
http://vyaskn.tripod.com/generate_s...e_sql_tasks.htm
I have an example at the above link.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:ORyFIPKTFHA.3056@.TK2MSFTNGP14.phx.gbl...
Hi,
For database users assigned with database role "db_datareader" and "Public".
Does it mean that he / she is able to browse all tables and execute Stored
Procedures. OR He / She can only execute Stored Procedures ?
If we want to give him / her access right to exec Stored Procedures only,
which database role should be assigned to him / her ?
Thanks

access result of "dynamic sql query" via transact sql

He,
want i want to do ist creating a dynamic query, execute it and access
the result via transact-sql.
e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
The first thing i tried was to use dynamic sql in udf's, but i realised
very fast, that this wont work.
After that I tried to build the query in a stored procedure but i can't
return the result set to a function or use it in an sql statement (like
SELECT * FROM (exec sp...)). I also tried it with temporary tables but
i also can't access them via userdefined functions. And i can't use
static names for the temp-Tables or even let the user exec the stored
procedure itself, because the user should not see how the whole thing
is working. He should just type "SELECT * FROM [function name]" and not
more.
So if somebody knows how to solve this problem .. please tell me
Thanks,
stephansteph
If I understood you correctly
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
"steph" <stephan@.aiche.info> wrote in message
news:1125924583.250239.32680@.o13g2000cwo.googlegroups.com...
> He,
> want i want to do ist creating a dynamic query, execute it and access
> the result via transact-sql.
> e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
> The first thing i tried was to use dynamic sql in udf's, but i realised
> very fast, that this wont work.
> After that I tried to build the query in a stored procedure but i can't
> return the result set to a function or use it in an sql statement (like
> SELECT * FROM (exec sp...)). I also tried it with temporary tables but
> i also can't access them via userdefined functions. And i can't use
> static names for the temp-Tables or even let the user exec the stored
> procedure itself, because the user should not see how the whole thing
> is working. He should just type "SELECT * FROM [function name]" and not
> more.
> So if somebody knows how to solve this problem .. please tell me
> Thanks,
> stephan
>|||this would work, but i think it won't work in a udf. but i need to do
it with a udf becaus my users just want to type
SELECT * FROM ... and not
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
SELECT * FROM #T
So is there any possibilty to do it with a udf ?|||steph
INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
"steph" <stephan@.aiche.info> wrote in message
news:1125925509.599913.104850@.g43g2000cwa.googlegroups.com...
> this would work, but i think it won't work in a udf. but i need to do
> it with a udf becaus my users just want to type
> SELECT * FROM ... and not
> CREATE TABLE #T
> (
> col INT
> )
>
> INSERT INTO #T EXEC myStoredProcedure
> SELECT * FROM #T
> So is there any possibilty to do it with a udf ?
>|||INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
not this way,
it won't work this way
create function dbo.udf ..
returns table
exec sp_creating_temp_table
return (select * from #created_temp_table)
so the user just have to type "SELECT * FROM dbo.udf WHERE .. "|||Please explain your requirement more fully and I'm sure someone can
suggest a better way. It isn't clear to me exactly why you want to do
this. Why can't you just write a query or create a view?
David Portas
SQL Server MVP
--|||I want to do a preselection like "SELECT * FROM ( dbo.udf(@.table_name,
@.other_param) ) WHERE ..." to accelerate the query. So i want to pass
the table name and the preselection params to the udf and the udf
returns the result set. The problem is i got to do some caltculations
for the preselection and then build the preselect query with the
calculated values and i think in this case a view or a selfwritten
query wont work ...
thanks
stephan|||Why not use a parameterized stored procedure? And by the way,
parameterizing table names is a really, really bad idea - and totally
unnecessary in a well-designed system.
David Portas
SQL Server MVP
--|||You didn't explain why you can't use a view or subquery. You can't use
dynamic code in a function.
Have you seen:
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dyn-search.html
Without more information all I can suggest is that you should review
your overall design - it sounds like a pretty odd setup to me. Have you
looked at middleware and BI tools?
David Portas
SQL Server MVP
--|||Stephan,
Can you explain what this "preselection" is (preferably with specific
examples - see http://www.aspfaq.com/etiquette.asp?id=5006).
In a well-designed database, it should not be necessary to jump
through hoops in order "to accelerate the query", whatever that
means.
Then again, if when you say "tables are dynamic," you mean
that you never know what tables exist at a given time, I think you
are in bigger trouble than if you were missing some indexes. I have
never seen a design that created and dropped tables willy-nilly that
was not little more than a huge mess.
Asking clear questions about a system like this is like asking
"What color is a chameleon?" Trying to manage one is like
trying to make clothes for amoebae. Nothing fits for more
than a few moments.
Steve Kass
Drew University
steph wrote:

>I already tried to use "parameterized stored procedure" but i can't
>access the result of a sp via t-sql so it won't work for a
>preselection.
>
>
>I know that it is not the best idea, but the tables in the db are
>dynamic, and also i want to use the functionality for more than one
>table and more then one db.
>thanks
>stephan
>
>

Access Not Executing Stored Procedure correctly

Hi guys I cant seem to get my stored procedure to execute properly through Access Xp. Do you think there is something wrong with my stored procedure??

CREATE PROCEDURE [insert_ConditionalLicense_UpdateFromTerms]
(@.TM_# [int],
@.FirstName [nvarchar](50),
@.LastName [nvarchar](50),
@.SS# [nvarchar](50),
@.Birthdate [nvarchar](50),
@.reasonforconditional [ntext],
@.Notes [ntext])

AS INSERT INTO [GamingCommissiondb].[dbo].[ConditionalLicense_View]
( [TM #],
[FirstName],
[LastName],
[SS#],
[reasonforconditional],
[ConditionalStart Date])


SELECT
[TM#],
[LASTNAME],
[FIRSTNAME],
[SSN#],
[NOTES],
[DATEOFCONDITIONAL]

FROM EmployeeGamingLicense
WHERE STATUS = 'TERMINATION-COND'
IF @.@.Error <> '0'
RETURN

when I execute it through a command button this is the message I get "paramater" not quite sure why I am getting this messageYou have to provide the parameters on the call you are making with the "on click" action. Otherwise, it will prompt you for them.

?|||Well I have the same kind of stored procedure on another form and it doesnt do that. I have a stored procedure on the Termination form that executes once the users press the command button. Hmm??|||No. There has to be some difference between the two. Look at them closely to find it.|||Ok I will take a closer look at my stored procedure. I'll try again thanks guys|||Ok I will take a closer look at my stored procedure. I'll try again thanks guys

Access Hangs on "Delete *" from linked table

I have a table called TKSD0UJA in my sqlserver that access links to.
The codes is:
strSel = "DELETE * FROM [TKSD0UJA];"
db.Execute strSel
it hands on db.Execute. I checked permissions within the sql server and
I have delete permission. Is there any reason why it should hang?Ari
Are you sure you don't need WHERE clause in the query?
How much does data exist in the table?
"Ari" <Kooshesh@.gmail.com> wrote in message
news:1130938827.948678.159380@.g44g2000cwa.googlegroups.com...
>I have a table called TKSD0UJA in my sqlserver that access links to.
> The codes is:
> strSel = "DELETE * FROM [TKSD0UJA];"
> db.Execute strSel
> it hands on db.Execute. I checked permissions within the sql server and
> I have delete permission. Is there any reason why it should hang?
>|||> The codes is:
> strSel = "DELETE * FROM [TKSD0UJA];"
You don't delete columns, you delete rows. So, the syntax "DELETE * FROM"
makes no sense. Try DELETE FROM tablename or DELETE tablename.
A|||much thanks

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe

Access Denied when trying to execute a ssis task

Hi,

I sometimes come accross this error when I attempt to execute an isolated task in the control flow. What is funny is that I am still able to debug the package.

It eventually resolves after a while. What could it be?

Thanks

Philippe

TITLE: Microsoft Visual Studio

Access Denied. (Exception from HRESULT: 0x80030005(STG_E_ACCESSDENIED))

BUTTONS:

OK

Are you using source control?

You usually get this error if you execute something that isn't checked out.

-Jamie

|||

Yep, I do use VSS 6

I have a hard time understanding a few things with VSS, i.e. I was running a package fine in BIDS but it failed in SSMS, The package seemed checked-in but the solution was checked out in another location under the same userid.

I did force the undo check out and it eventually went fine. Now, SSMS is running the version I want.

I yet have to understand exactly how VSS Works. Right now, I am confused.

Philippe

|||The point is that you shouldn't have to check an SSIS package out of source control (we use TFS here) in order to execute it. This mis-feature is still present 15 months after Philippe's message, and still very frustrating!|||

I agree.

Have you reported this at Connect?

-Jamie

|||

Hi,

I have dropped VSS. I now use my company "official" source control, CVS, along with a couple third party tools like Tortoise, Smart CVS and CVSSCC which gives me some integration right from dev tools.

All that source control stuff is still really not user friendly, because of that I keep multiple backups of my stuff.

This has saved my life a couple times.

I thought the point of using any source control was to free the developer of any concern about these things and let him focus on development. I was wrong.

My IT chose CVS only because it is free, not because it is good, on the top of that they run an outdated unix version on a very old box.

I have a hard time with this while it is supposed to be "safer".

I need to be really carefull with this, not feeling safe anyhow.

Does any one has a success story to share as far as source control of any kind is concerned?

- Multiple developers on the same project

- Deployment of specifc versions by a dba team

- roll back to previous versions

- branches management

- never loosing any file/version on your working folder

- no fuss with roots/modules

- no issues with caps/no caps version fo the same module

- always get the project to the right root/module

- able to easilly do spring cleaning of the repository

Thanks,

Philippe