SERVERPROPERTY Conundrum

• LerxtDBA

  SSCrazy

  Points: 2190

  More actions

  June 2, 2015 at 1:23 pm

  #317890

  I manage a hosted environment - several actually. Meaning my company is the host. We provide some of our customers with a dedicated instance of SQL Server that they can use in read-only mode. Unfortunately, I have some very inquisitive customers who probably should be hosting their own SQL. They like to poke into the inner workings of our environment in ways that make me uncomfortable. What I originally found was that they were reading our server registry. I've disabled that. The next thing that bothers me is the amount of information available to them through SERVERPROPERTY. They don't need to know things like physical node of a cluster their instance is on, or the last time we updated SQL, or our licensing choices.

  So my question is this. Is there any way to prevent a user from using the SERVERPROPERTY function? I've done a lot of digging and can't seem to find a way to do it.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 2, 2015 at 1:29 pm

  #1802426

  wow, my knee jerk reaction was that it's probably requires VIEW SERVER STATE, but i just proved it is available to any user in the public role.

  here im testing server properties and database properties.

  CREATE USER TestingGuy WITHOUT LOGIN

  EXECUTE AS USER='TestingGuy'

  SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue

  UNION ALL

  SELECT 'Collation', SERVERPROPERTY('Collation')

  UNION ALL

  SELECT 'CollationID', SERVERPROPERTY('CollationID')

  UNION ALL

  SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')

  UNION ALL

  SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS')

  UNION ALL

  SELECT 'Edition', SERVERPROPERTY('Edition')

  UNION ALL

  SELECT 'EditionID', SERVERPROPERTY('EditionID')

  UNION ALL

  SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')

  UNION ALL

  SELECT 'InstanceName', SERVERPROPERTY('InstanceName')

  UNION ALL

  SELECT 'IsClustered', SERVERPROPERTY('IsClustered')

  UNION ALL

  SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')

  UNION ALL

  SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')

  UNION ALL

  SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')

  UNION ALL

  SELECT 'LCID', SERVERPROPERTY('LCID')

  UNION ALL

  SELECT 'LicenseType', SERVERPROPERTY('LicenseType')

  UNION ALL

  SELECT 'MachineName', SERVERPROPERTY('MachineName')

  UNION ALL

  SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')

  UNION ALL

  SELECT 'ProcessID', SERVERPROPERTY('ProcessID')

  UNION ALL

  SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')

  UNION ALL

  SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')

  UNION ALL

  SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')

  UNION ALL

  SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')

  UNION ALL

  SELECT 'ServerName', SERVERPROPERTY('ServerName')

  UNION ALL

  SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')

  UNION ALL

  SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')

  UNION ALL

  SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')

  UNION ALL

  SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')

  SELECT

  DATABASEPROPERTYEX(db_name(),'Collation') AS Collation,

  DATABASEPROPERTYEX(db_name(),'ComparisonStyle') AS ComparisonStyle,

  DATABASEPROPERTYEX(db_name(),'IsAnsiNullDefault') AS IsAnsiNullDefault,

  DATABASEPROPERTYEX(db_name(),'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,

  DATABASEPROPERTYEX(db_name(),'IsAnsiPaddingEnabled') AS IsAnsiPaddingEnabled,

  DATABASEPROPERTYEX(db_name(),'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,

  DATABASEPROPERTYEX(db_name(),'IsArithmeticAbortEnabled') AS IsArithmeticAbortEnabled,

  DATABASEPROPERTYEX(db_name(),'IsAutoClose') AS IsAutoClose,

  DATABASEPROPERTYEX(db_name(),'IsAutoCreateStatistics') AS IsAutoCreateStatistics,

  DATABASEPROPERTYEX(db_name(),'IsAutoShrink') AS IsAutoShrink,

  DATABASEPROPERTYEX(db_name(),'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,

  DATABASEPROPERTYEX(db_name(),'IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,

  DATABASEPROPERTYEX(db_name(),'IsFulltextEnabled') AS IsFulltextEnabled,

  DATABASEPROPERTYEX(db_name(),'IsInStandBy') AS IsInStandBy,

  DATABASEPROPERTYEX(db_name(),'IsLocalCursorsDefault') AS IsLocalCursorsDefault,

  DATABASEPROPERTYEX(db_name(),'IsMergePublished') AS IsMergePublished,

  DATABASEPROPERTYEX(db_name(),'IsNullConcat') AS IsNullConcat,

  DATABASEPROPERTYEX(db_name(),'IsNumericRoundAbortEnabled') AS IsNumericRoundAbortEnabled,

  DATABASEPROPERTYEX(db_name(),'IsParameterizationForced') AS IsParameterizationForced,

  DATABASEPROPERTYEX(db_name(),'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,

  DATABASEPROPERTYEX(db_name(),'IsPublished') AS IsPublished,

  DATABASEPROPERTYEX(db_name(),'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,

  DATABASEPROPERTYEX(db_name(),'IsSubscribed') AS IsSubscribed,

  DATABASEPROPERTYEX(db_name(),'IsSyncWithBackup') AS IsSyncWithBackup,

  DATABASEPROPERTYEX(db_name(),'IsTornPageDetectionEnabled') AS IsTornPageDetectionEnabled,

  DATABASEPROPERTYEX(db_name(),'LCID') AS LCID,

  DATABASEPROPERTYEX(db_name(),'Recovery') AS Recovery,

  DATABASEPROPERTYEX(db_name(),'SQLSortOrder') AS SQLSortOrder,

  DATABASEPROPERTYEX(db_name(),'Status') AS Status,

  DATABASEPROPERTYEX(db_name(),'Updateability') AS Updateability,

  DATABASEPROPERTYEX(db_name(),'UserAccess ') AS UserAccess ,

  DATABASEPROPERTYEX(db_name(),'Version') AS Version

  SELECT

  DATABASEPROPERTYEX(db_name(),'Collation') AS Collation,

  DATABASEPROPERTY(db_name(),'IsAnsiNullDefault') AS IsAnsiNullDefault,

  DATABASEPROPERTY(db_name(),'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,

  DATABASEPROPERTY(db_name(),'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,

  DATABASEPROPERTY(db_name(),'IsAutoClose') AS IsAutoClose,

  DATABASEPROPERTY(db_name(),'IsAutoCreateStatistics') AS IsAutoCreateStatistics,

  DATABASEPROPERTY(db_name(),'IsAutoShrink') AS IsAutoShrink,

  DATABASEPROPERTY(db_name(),'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,

  DATABASEPROPERTY(db_name(),'IsBulkCopy') AS IsBulkCopy,

  DATABASEPROPERTY(db_name(),'IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,

  DATABASEPROPERTY(db_name(),'IsDboOnly') AS IsDboOnly,

  DATABASEPROPERTY(db_name(),'IsDetached') AS IsDetached,

  DATABASEPROPERTY(db_name(),'IsEmergencyMode') AS IsEmergencyMode,

  DATABASEPROPERTY(db_name(),'IsFulltextEnabled') AS IsFulltextEnabled,

  DATABASEPROPERTY(db_name(),'IsInLoad') AS IsInLoad,

  DATABASEPROPERTY(db_name(),'IsInRecovery') AS IsInRecovery,

  DATABASEPROPERTY(db_name(),'IsInStandBy') AS IsInStandBy,

  DATABASEPROPERTY(db_name(),'IsLocalCursorsDefault') AS IsLocalCursorsDefault,

  DATABASEPROPERTY(db_name(),'IsNotRecovered') AS IsNotRecovered,

  DATABASEPROPERTY(db_name(),'IsNullConcat') AS IsNullConcat,

  DATABASEPROPERTY(db_name(),'IsOffline') AS IsOffline,

  DATABASEPROPERTY(db_name(),'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,

  DATABASEPROPERTY(db_name(),'IsReadOnly') AS IsReadOnly,

  DATABASEPROPERTY(db_name(),'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,

  DATABASEPROPERTY(db_name(),'IsShutDown') AS IsShutDown,

  DATABASEPROPERTY(db_name(),'IsSingleUser') AS IsSingleUser,

  DATABASEPROPERTY(db_name(),'IsSuspect') AS IsSuspect,

  DATABASEPROPERTY(db_name(),'IsTruncLog') AS IsTruncLog,

  DATABASEPROPERTY(db_name(),'Version') AS Version

  REVERT

  DROP USER TestingGuy

  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!