macros in sql server

I have got all of my tables running on agent jobs, but some of my queries have temporary tables and won't run on jobs because of  an error.  If you run them manually,  they complete fine, but have some permission errors and agent jobs are unforgiving.  That's why I'm asking around to see if there is another way to automate my queries.

Providing the startup account for the SQL Server Agent service has the right permissions then there is no reason you cannot execute your scripts from there.  Where we should start is for you to tell us the error you are getting.

Alternatively, if you do not want to use the SQL Server Agent, then you will have to use some other scheduling program such as the built in Windows Task Scheduler, so that you can execute you scripts using SQLCmd.exe.

additionally to the above post about SQL Server Agent that can help you, you can use "home made" or 3 rd party tools :
e.g
JAMS Enterprise Job Scheduler
http://www.jamsscheduler.com/

The sql agent looks like the  best for you.

So, The answer is  "yes", you just need to be more specific about what would you like to do

more
SQL Server Agent Introduction
http://www.youtube.com/watch?v=9j91paAoSVk

Thanks for your help.  I appreciate it.  

The error in agent when I run the query says: Cannot drop the table 'Temp_Loan' because it does not exist or you do not have permission. [SQLSTATE 42S02] [ERROR 3701].  The Step failed.  SQL Severity 11.

Thanks again for your help

error in my last comment, should say agent job instead of query
Thanks for your help.  I appreciate it.  

The error in agent when I run the AGENT job says: Cannot drop the table 'Temp_Loan' because it does not exist or you do not have permission. [SQLSTATE 42S02] [ERROR 3701].  The Step failed.  SQL Severity 11.

Thanks again for your help

That means that the account used by the SQL Server Agent service does not have the appropriate permissions.  So here are the steps you need to take:
1.  Identify the Windows account used for the SQL Server Agent.
2.  Identify the SQL Server Login using that account.
3.  Find the user in the database that is using this Login
4.  Make sure that if the Login does not have the correct permissions that the user does.

I don't get it.  I am set up as SysAdmin and Public under Roles, and I automate the transfer of the data from the core everyday through the agent.  Why wouldn't I be able to run these queries.  I can automate some queries, just not the ones with temporary tables.  Maybe I am super confused.  Any further help would be appreciated.

Thanks,

<The error in agent when I run the query says: Cannot drop the table 'Temp_Loan' because it does not exist or you do not have permission. [SQLSTATE 42S02] [ERROR 3701].  The Step failed.  SQL Severity 11.>
2nd part
you may not run this "drop table" in the right DB.
try to use:

USE YOURDB

DROP TABLE Temp_Loan'


---
Also did you try to run all this sql agent code from SSMS ?

I don't get it.  I am set up as SysAdmin and Public under Roles,
You could be God, that is not relevant, what matters is the Windows account used by the SQL Server Agent service.  Do you need help finding that?

I am not sure how to find my set up in Server Agent Service.  Can you help me please.

Thanks again for you patience and help.

Also did you try to run all this sql agent code from SSMS ?

What is SSMS?  I guess I have a lot of learning to do.

I am not sure how to find my set up in Server Agent Service.  Can you help me please.
1. Execute "Services.msc" from the Start menu or from the command line.
2. Find the SQL Server Agent (MSSQLSERVER) service and double click on it.
3. Click on the LogOn tab
4. Let us know if you are using the "Local System Account" or "This Account".
5. If it is this account that is the Login that requires the appropriate permissions.

What is SSMS?
SQL Server Management Studio

Thank you very much.  I followed the steps below, then went to Security Logins, then the Logon name for mssqlserver.  I right clicked went to Properties, then the User Mapping.  All of the tables are mapped.  The database role is Public.  Is that a problem?  If so, what other roles should it have?

Thanks again

Right now, I am set up as the db.owner, I am trying that for the mssqlserver logon.  So I set the roles to public and db.owner for user Mapping for mssqlsserver.  I'm  running one of the simple temporary tables as a job right now to see if it works.

Thanks

So you have identified the account used by the SQL Server Agent service.  We will call it SQLServerAgentAccount (for short).

You then went into SSMS and in the Security object confirmed the Login and all the server roles that the SQLServerAgentAccount belongs to.

You then went to the database and found the user mapped to the SQLServerAgentAccount Login.  We will call it SQLServerAgentUser.

You then wen to the Database Security object and you identified that it only belonged to the Public database role.

Finally to triple check that you had not overlooked anything you logged on to SQL Server as the  SQLServerAgentAccount and attempted to run the scripts from the job.

Is that correct?  If so, and if you are still getting an error can you tell us the output to those steps.

I've requested that this question be closed as follows:

Accepted answer: 0 points for mathburg's comment #a39088399

for the following reason:

I appreaciate all of your help, and while I couldn't find a solution from your hlep, I learned a lot about how to look up permissions and find out who's logon is doing what. It works beautifully now.

Thanks again for all your help

Moderator,

I believe if you review the question, I answered the original question in my first comment.  In fact the author ended up using exactly this solution to solve the problem.

Thanks,
Anthony

(C)  http:#a39054225