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: 8,929

    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,642

    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,530

    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: 8,929

    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,530

    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,491

    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,642

    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: 8,929

    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: 522

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