1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL SP Question - permissions

Discussion in 'HTML, Graphics & Programming' started by AHarvey, Oct 12, 2018.

  1. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,248

    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.
     
  2. howler

    Wise Guy

    Joined: Oct 5, 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
     
  3. touch

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,867

    Location: Sufferlandria

    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.
     
  4. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,248

    Location: Stoke area

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

    Capodecina

    Joined: Oct 28, 2006

    Posts: 10,867

    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.
     
  6. tom_e

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 25,821

    Location: West mids

    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.
     
  7. howler

    Wise Guy

    Joined: Oct 5, 2004

    Posts: 1,647

    Sorry yes that is what I meant, that there are permissions on a stored procedure as in what users can run / execute it
     
  8. AHarvey

    Sgarrista

    Joined: Mar 6, 2008

    Posts: 9,248

    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 ****)
     
  9. fretted

    Hitman

    Joined: Jan 4, 2010

    Posts: 535

    Don't you have a business intelligence server solution you can use? Far easier that going down the SSIS route...
     
  10. OspreyO

    Mobster

    Joined: Dec 12, 2006

    Posts: 3,058

    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.
     
  11. OspreyO

    Mobster

    Joined: Dec 12, 2006

    Posts: 3,058

    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.
     
  12. tom_e

    Man of Honour

    Joined: Dec 26, 2003

    Posts: 25,821

    Location: West mids

    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.
     
  13. OspreyO

    Mobster

    Joined: Dec 12, 2006

    Posts: 3,058

    Rewriting them anyway. But I take your point.