Post reply

Unsure as to what permission would be best

  • November 20, 2012 at 4:55 pm

    #266307

    Currently, I have the following query I'm running across multiple SQL Servers to get some basic stats.

    At the moment, I'm using a new with "serveradmin" permission in order to get this data, but I'd like to lock it down more permissions wise.

    Does anyone have any suggestions on what permissions I could lock it down to?

    with

    HostInfo as

    (Select physical_memory_in_bytes/(1024*1024) AS [PhysicalMemory],cpu_count AS [CPUCount] FROM sys.[dm_os_sys_info]),

    MinMemory as

    (Select value AS [MinMemory] FROM master.sys.sysconfigures WHERE comment = 'Minimum size of server memory (MB)'),

    MaxMemory as

    (Select value AS [MaxMemory] FROM master.sys.sysconfigures WHERE comment = 'Maximum size of server memory (MB)')

    select

    SERVERPROPERTY('ServerName') AS MachineInstanceName,

    SERVERPROPERTY('MachineName') AS MachineName,

    SERVERPROPERTY('InstanceName') AS InstanceName,

    SERVERPROPERTY('Edition') AS EditionOfSQLServer,

    SERVERPROPERTY('ProductVersion') AS ProductBuildVersion,

    SERVERPROPERTY('Collation') AS Collation,

    RIGHT(@@version, LEN(@@version)- 3 -charindex (' ON ', @@VERSION)) AS OperatingSystem,

    SERVERPROPERTY('IsClustered') AS IsClustered,

    PhysicalMemory,

    CPUCount,

    MinMemory,

    MaxMemory

    from HostInfo,MinMemory,MaxMemory

  • November 21, 2012 at 1:22 am

    #1561113

    Hi Philip,

    Didnt understand what u have asked?? can explain opnce again?

    "At the moment, I'm using a new with "serveradmin" permission in order to get this data, but I'd like to lock it down more permissions wise"

  • November 21, 2012 at 1:28 am

    #1561116

    PhilipC (11/20/2012)

    Currently, I have the following query I'm running across multiple SQL Servers to get some basic stats.

    At the moment, I'm using a new with "serveradmin" permission in order to get this data, but I'd like to lock it down more permissions wise.

    Does anyone have any suggestions on what permissions I could lock it down to?

    That should just be View Server State and nothing else.

    p.s. master.sys.sysconfigures is deprecated, included only for backward compat with SQL 2000, should not be used. sys.configurations

    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

  • November 21, 2012 at 1:57 pm

    #1561395

    Thanks Gail, that did the trick 🙂

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

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