SQL SP Question - permissions

[AHarvey]

Hi all,

I've been asked to automate a report that we have to manually run each week.

I've got the SQL from the report, save it to a ##table.

I then BCP this to a shared drive as a CSV file using: EXEC master..xp_cmdshell

I then email this out using: EXEC msdb.dbo.sp_send_dbmail

The problem I have is that the BCP cmd only works with our Dataload account, it's like an admin account (i have no idea if this is just our name for it or if it's universal)

The email part will only work when I connect with my personal windows account.

I've a few other tweaks I need to do but does anyone know with what permissions an SP runs? It'll be running via an agent job. Never touched anything like this and there's a distinct lack of decent tutorials for TSQL. It's either noob level selects or it's like reading a technical journal that's been translated from Japanese to Dutch to English.

Can you set permissions in an SP? Can you switch them/reconnect halfway through or would looking at 2 SP's be better?

My other option is DTS packages. Yes, we still use them but I think they want to get rid asap.

 

[AHarvey]

Do you mean they dont have permissions associated (typo)?
There are permissions for who can read/write/execute a procedure but there isn't permissions for that SP of what it can then do - it takes that permission from the user executing it.

I think all you'd need to do is add your Dataload account to the DBMailUser role in the msdb.

What if there isn't a user executing it but it's done via an automated job at a set time each week?

 

[AHarvey]

Thanks for all the feedback everyone, going to spend this week trying to get it working while working all the other calls I've got.

On a side note, does anyone have any suggestions for decent video tutorials or up to date books that will help me with SQL/TSQL/SSMS/SSIS/SSRS/DTS?

Our system is massive, people have been here 6 years and are very good at their role but all admit they don't know the system fully. My investigation skills are pretty good, I often know what the problem is going to be before the having to investigate but my SQL and surround knowledge needs some work.

I am confident with your basics, using joins, declares etc but it's the more complex subjects that I want to develop on. String manipulation, CAST, Loops etc. I can knock up basic Python scripts and can often look at code and figure out what it's trying to do but I really want to be at the top of the game (without sounding like a ****)