Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: Getting version information from SQL server

    Hello,

    I'm trying to get the version information for my SQL servers. Specifically I want to get the major, minor, release and build numbers separately.

    Does anyone know what the recommended method for doing this is?

    I tried using @@microsoftversion but I can't find any documentation about it.

    @@VERSION has the information in it but I'd have to parse the text, not something I want to do and it means the code can easily be broken if the format of the string changes.

    xp_msver has what I need but I want to run this as a T-SQL batch so I can't access the results of xp_msver.

    Just to add another restriction the code should be compatible with SQL server 2000 and, if possible SQL 7.0 (although not necessary).

    Basically I have a script that I want to run on my servers that removes all users except dbo and guest from my databases. At the moment I query master.dbo.sysxlogins table but this doesn't work on 2005 so I thought I could check the version and run a different query for those servers.

    Any help appreciated.
    Kep.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by kep
    Basically I have a script that I want to run on my servers that removes all users except dbo and guest from my databases.
    Can't you simply run sp_who (or sp_who2) to find out who is connected?

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Code:
    SELECT SERVERPROPERTY ('edition') 
    SELECT SERVERPROPERTY('productversion')  <== I think this is the one you want
    SELECT SERVERPROPERTY ('productlevel')
    Perhaps not everything you wanted, since you have to parse the result. However, if you combine it with the PARSENAME function, you might find it useful (eg,
    SELECT parsename(cast(SERVERPROPERTY('productversion') as varchar(255)),4))

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Nov 2007
    Posts
    3
    That's exact what I'm after hmscott, thanks.

    shammat: Sorry, didn't make it clear in my original post. I don't want to log the user off I want to remove access to the database. Code I had for SQL 2000 is:
    Code:
    DECLARE @userName  SYSNAME
    DECLARE @userCount INTEGER
    
    SELECT @userCount = COUNT(*)
        FROM sysusers AS usr
        WHERE usr.islogin = 1
            AND usr.hasdbaccess = 1
            AND NOT(usr.name IN ('dbo', 'guest'))
    
    WHILE @userCount > 0
    BEGIN
        SELECT TOP 1 @userName = usr.name
            FROM sysusers AS usr
            WHERE usr.islogin = 1
                AND usr.hasdbaccess = 1
                AND NOT(usr.name IN ('dbo', 'guest'))
    
        EXEC sp_revokedbaccess @userName
        SET @userCount = @userCount - 1
    END
    GO

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •