Post reply

Powershell Script to verfiy showplan enabled on SQL Server 2012

  • May 18, 2015 at 10:39 pm

    #297095

    I am looking for a sample PowerShell script that allows me to verify that showplan is enabled for a user on a SQL Server 2012 instance. Haven't figured out how to code it.

  • May 19, 2015 at 5:38 am

    #1798973

    well, start at SQL first.

    do you want to test implied/inferred permissions, or just explicitly granted permissions?

    sysadmin, or anyone in a db_owner roles would of course have showplan permissions in the database in question, and then you can test for explicitly added permissions where someone rant GRANT SHOWPLAN TO [mydomain\myuser]

    also, showplan is inherited if somone was granted ALTER TRACE, or VIEW SERVER STATE as well.

    what if someone granted showplan to a role the user happens to be in? or a nested role within a role?

    there's lots of permissions scripts out in the scripts section, i'd dig through there instead of reinventing the wheel, and then i could call that script via powershell.

    the place to start is mapping what permissions youa re after...just explicitygrants, or implied/inherited as well.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 25, 2015 at 1:37 pm

    #1800438

    I wanted to test the explicitly granted showplan permissions.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply