• Michael L John - Saturday, June 24, 2017 5:45 AM

    Jeff Moden - Friday, June 23, 2017 8:21 PM

    Michael L John - Thursday, June 22, 2017 9:27 AM

    I created a domain account, and added it to the proper groups on the server.
    I added this account to SQL, made it sysadmin, and disabled the login.
    I created the proc with the EXECUTE AS this user.

    Really not the right way to do this.  The proxy user should be a rather mindless AD user with virtually no privs.  Period.  Then you don't have to disable the login.

    The database that you're using should be owned by the disabled "sa" user.  Of course, the SQL Server login should be able to "see" where it is that you want it to "see".

    The only privs the users should need is "public", "connect", and privs to execute the proc.  For sure, you must NEVER give the privs to execute xp_CmdShell. PERIOD!.  The proc should have EXECUTE AS OWNER. 

    Heh... maybe I should submit my presentation on how to setup and use xp_CmdShell for the Pittsburgh SQL Saturday coming up?

    Actually, after I looked at this whole thing again, the account is not sysadmin,  It has only been granted rights to xp_cmdshell, and read\write access to the logging table in the utility database.  

    What about a presentation of "a bunch of SQL Spackle like" things to make life easier?

    Ya know... that sounds like it might be a good idea.  I've done that in the past for my local chapter.  I called it "Tool Time" but we could call it whatever you folks suggest.  It had things like a couple of automated reports (Morning "Jobs" report, and Enterprise Wide disk report/warning system and it does NOT require SSRS!), a mail system that DOESN'T require you to setup mail in the database (if you're in a hurry), a killer blocking monitor system that captures and list any blocking in a very meaningful hierarchical order along with the code that's involved, backup code that auto-magically does backups even on new databases, a clickable PerfMon button that would display the last half hour of various performance indicators in real time, how to correctly setup xp_CmdShell for safe usage, and a couple of useful procs (sp_WhatsRunning and sp_ShowWorst) that help you quickly find your worst performing queries, which may not actually be your longest running queries.  Might be good for the "So you wanna be a DBA track" that you have.  Could easily cover 2 sessions if you want some useful details.  Lemme know and I'll put the pieces together in a presentation along with an abstract.

    I don't know if I'll have it ready in time but I'm working on one about why you don't ever have to rebuild/reorganize indexes and a great way to find used stats, which also lets you find and drop unused stats, which is all that you really need to do instead of a bunch of index maintenance that may actually cause slowdowns and blocking when it's done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)