Permissions needed to see server properties?

  • What are the permissions needed to see server level properties?

    We have a user who sent us a screen shot of the server properties window, which I was sure he wasn't able to see. I need to find out how he got these permissions.

    EDIT: When I google this, I get Microsoft's "How to change server properties" pages and what permissions are needed to see database properties, but nothing on the permissions needed to see server properties.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • VIEW SERVER STATE

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thank you.

    Now I need to find out how the user got that permission.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (8/12/2015)


    Thank you.

    Now I need to find out how the user got that permission.

    Oh, good grief. Apparently VIEW SERVER STATE is granted to anyone with the public role. In otherwords, anyone with permission to connect to the server.

    SELECT * FROM sys.server_permissions sp

    INNER JOIN sys.server_principals spr

    ON sp.grantee_principal_id = spr.principal_id

    WHERE sp.type = 'VWSS';

    --locate specific server level permissions

    Is this a default SQL Setting?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.

    Edit: in SQL 2012. I don't think it's different in 2008/2008 R2

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (8/12/2015)


    It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.

    Thank you. I'll have to look into this then.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • GilaMonster (8/12/2015)


    It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.

    Edit: in SQL 2012. I don't think it's different in 2008/2008 R2

    Just checked on my SQL2008R2 installs, and the only "accounts" with VIEW SERVER STATE are the ## replication and policy logins.

  • jasona.work (8/12/2015)


    GilaMonster (8/12/2015)


    It is not. By default public has VIEW ANY DATABASE (which means they can see the names, not that they have access to them) and CONNECT on all endpoints. That's it.

    Edit: in SQL 2012. I don't think it's different in 2008/2008 R2

    Just checked on my SQL2008R2 installs, and the only "accounts" with VIEW SERVER STATE are the ## replication and policy logins.

    Those have the permissions too. But I'm trying to figure out how most of our servers ended up with it added as a public permission. Corporate installed these servers, so I'm wondering if they did it or if, for some reason, we did it here back in the day. I'm waiting for my boss to get in so I can ask him.

    Although... I wonder if they added it because they've got several audit jobs on the servers...

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 8 posts - 1 through 7 (of 7 total)

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