Post reply

Query system tables or views to find method of authentication

  • July 2, 2007 at 9:35 am

    #372678

    What system table would I need to query to find out what method of authentication an instance of SQL 2005 is using?  This is a piece of info I am gathering through an SSIS package to automatically update inventory table.

  • July 2, 2007 at 10:13 am

    #716582

    You can play with this query, this table stores values that have been set up during installation:

    select * from master.dbo.spt_values

    where name ='trusted'

    Unfortunately, in our environment all servers are mixed mode so I could not compare them. If you will find the difference, please post results here too.

    Thanks

     

  • July 2, 2007 at 10:51 am

    #716600

    I queried both kinds of servers and it returned the same values for each.  I don't think that is referring to the instance auth method.  Thanks for the info though, I wasn't aware of what that table was before.

  • July 2, 2007 at 11:24 am

    #716606

    I found a function that can be used to look up info about the instance, including the info I am looking for.  The info is posted below.

    SERVERPROPERTY

    Display information about the server instance.

    Syntax:      SERVERPROPERTY ( propertyname )

    For boolean values ServerProperty will return 1 for True or 0 for false.

    Examples

    SELECT CONVERT(char(20), SERVERPROPERTY('BuildClrVersion'));SELECT CONVERT(char(20), SERVERPROPERTY('Collation'));SELECT CONVERT(char(20), SERVERPROPERTY('CollationID'));SELECT CONVERT(char(20), SERVERPROPERTY('ComparisonStyle'));SELECT CONVERT(char(20), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'));SELECT CONVERT(char(20), SERVERPROPERTY('Edition'));SELECT CONVERT(char(20), SERVERPROPERTY('EditionID'));SELECT CONVERT(char(20), SERVERPROPERTY('EngineEdition'));SELECT CONVERT(char(20), SERVERPROPERTY('InstanceName'));SELECT CONVERT(char(20), SERVERPROPERTY('IsClustered'));SELECT CONVERT(char(20), SERVERPROPERTY('IsFullTextInstalled'));SELECT CONVERT(char(20), SERVERPROPERTY('IsIntegratedSecurityOnly'));SELECT CONVERT(char(20), SERVERPROPERTY('IsSingleUser'));SELECT CONVERT(char(20), SERVERPROPERTY('LCID'));SELECT CONVERT(char(20), SERVERPROPERTY('LicenseType'));SELECT CONVERT(char(20), SERVERPROPERTY('MachineName'));SELECT CONVERT(char(20), SERVERPROPERTY('NumLicenses'));SELECT CONVERT(char(20), SERVERPROPERTY('ProcessID'));SELECT CONVERT(char(20), SERVERPROPERTY('ProductVersion'));SELECT CONVERT(char(20), SERVERPROPERTY('ProductLevel'));SELECT CONVERT(char(20), SERVERPROPERTY('ResourceLastUpdateDateTime'));SELECT CONVERT(char(20), SERVERPROPERTY('ResourceVersion'));SELECT CONVERT(char(20), SERVERPROPERTY('ServerName'));SELECT CONVERT(char(20), SERVERPROPERTY('SqlCharSet'));SELECT CONVERT(char(20), SERVERPROPERTY('SqlCharSetName'));SELECT CONVERT(char(20), SERVERPROPERTY('SqlSortOrder'));SELECT CONVERT(char(20), SERVERPROPERTY('SqlSortOrderName'));

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

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