SQL SP Question - permissions

Soldato
Joined
6 Mar 2008
Posts
10,078
Location
Stoke area
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.
 
Associate
Joined
5 Oct 2004
Posts
1,647
SP's have permissions associated with them, I would assume if running the SP from the agent that it will take the permissions for the user context that the SQL agent runs as

DTS is old hat and I think they have been trying to force it out for some time

For email, I've not used the SP you state but if you had open relay available on your SMTP server you might be able to get something to work
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
SP's have permissions associated with them
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.
 
Soldato
OP
Joined
6 Mar 2008
Posts
10,078
Location
Stoke area
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?
 
Soldato
Joined
28 Oct 2006
Posts
12,456
Location
Sufferlandria
It has to run under some account. Maybe a service account or account details were entered when the job was created. Could also be specified in the database connection string, depends how the job works.
 
Man of Honour
Joined
26 Dec 2003
Posts
30,835
Location
Shropshire
As touch says there's always a user running it automated or not SPs themselves don't have permissions it's down to the user that is executing them.

Ideally your service user which is the one used to trigger scheduled jobs has access to all the things needed as you can't flip the user half way through the execution and using a standard user account to run scheduled jobs is bad drills.

If you want to move from SP then SSIS is the way forward DTS jobs are very old hat these days, although I've used both and for most things they're just as good as each other.
 
Associate
Joined
5 Oct 2004
Posts
1,647
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.

Sorry yes that is what I meant, that there are permissions on a stored procedure as in what users can run / execute it
 
Soldato
OP
Joined
6 Mar 2008
Posts
10,078
Location
Stoke area
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 ****)
 
Soldato
Joined
12 Dec 2006
Posts
5,129
Why email stuff around? Thast old thinking. Information should not be silo'ed in email.

You need a reports server and website, or business intelligence server.

Also a DBA/Server Admin should be setting up the infrastructure and granting permissions.
Your agents and job should not be running under personal user accounts. You should have a agent or application or report user account.

Also think of if someone gets run over by a bus, their login disappears, how does the system keep going, how does someone take over and cover that persons job, if everything is run from that persons account.
 
Soldato
Joined
12 Dec 2006
Posts
5,129
...
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 ****)

I thought using loops or Cursors is generally bad in SQL. Use only if you have to, and generally there is a better way.
I would have thought strings and casting is the easier part of SQL. I find advanced joins much trickier, joining a table on itself in different ways multiple times, or having a join within a join.

I spend more time thinking of the logic of the data than I do the SQL. If I get stuck in SQL there's always an example on the web somewhere.
If you have a senior contractor on site, get friendly with them and pick their brain. They usually know it all inside and outside. They will however try avoid distractions form the paying gig.
 
Man of Honour
Joined
26 Dec 2003
Posts
30,835
Location
Shropshire
I thought using loops or Cursors is generally bad in SQL. Use only if you have to, and generally there is a better way.
I would have thought strings and casting is the easier part of SQL. I find advanced joins much trickier, joining a table on itself in different ways multiple times, or having a join within a join.
There are generally better ways to do things but it's still useful to know they exist and the basics of how they work as you may end up supporting them in others code.
 
Back
Top Bottom