SQL Server Performance

xp_fixeddrives

Discussion in 'SQL Server 2005 General DBA Questions' started by MichaelB, May 19, 2008.

  1. MichaelB Member

    Ok guys, my turn. I wanted to see if any of you know how to get drive space (like using xp_fixeddrives) for all linked servers. I want to pull that info into one table on one server. I know I can do this using openquery, but the thing is that the linked server logins would have to be sysadmin for any results tobe returned (in a 2005 environent). any thoughts? I do not want to have the linked servers to have sysadmin rights.
    Mike
    MichaelB, May 19, 2008
    #2

  2. satya Moderator

    satya, May 21, 2008
    #3

  3. MichaelB Member

    It wont work with 2005 and a non sysadmin login on the linked server
    MichaelB, May 21, 2008
    #4

  4. MichaelB Member

    Oh well. I guess no one knows:(
    MichaelB, May 23, 2008
    #5

  5. Kewin New Member

    [quote user="MichaelB"]
    Oh well. I guess no one knows:([/quote]
    Well, this is one of the procs that is regarded as 'dangerous' from a security standpoint.
    The question may well be, do we want anyone to be able to gather info about the drives?
    ...or is it something that only users of sufficient level (and thus also presumably trusted) should be able to get?
    I don't know for sure, but I think that the user needs o/s level permissions, since it's the o/s that supplies the information
    through xp_fixeddrives

    /Kenneth
    Kewin, May 26, 2008
    #6

  6. satya Moderator

    .. absolutely the change of security features introduced in SQL 2005 will have such breakup of scripts that weren't documented before, another best example for not using undocumented stored procedures [:)}.
    satya, May 26, 2008
    #7

  7. MichaelB Member

    Yah, the linked servers are 2005. I think it should be able to give disk space. sigh. I understand it may be an all or nothing proposition. Not a critical thought. Are there any DMVs I can use maybe?? :(
    MichaelB, May 27, 2008
    #8

  8. Kewin New Member

    Hmm... Well, I think that you may need to go outside SQL Server, since after all, the drives isn't within SQL Server's domain anyway.
    All that is handled by the o/s.
    What about WMI?
    /Kenneth
    Kewin, May 28, 2008
    #9

  9. satya Moderator

    IF the associated servers are 2005 then you could make use of EXECUTE AS process in order to get complete information, also in case of using DMVs on multiple serves.
    satya, May 28, 2008
    #10

  10. MichaelB Member

    Kenneth - I will look into WMI
    Satya - I tried that and got the same error. I thought it would work too!
    MichaelB, May 28, 2008
    #11

  11. satya Moderator

    Does all thoe Linked servers are in SqL 2005?
    satya, May 24, 2008
    #12

  12. satya Moderator

    Hmm, it works for me.
    Try using the following script and create in relevant SQL Server to extract required information using EXECUTE AS method:
    CREATE TABLE #fix
    (Drive NVARCHAR(5),
    MBFree INT)

    INSERT INTO #fix
    EXEC xp_fixeddrives

    SELECT * FROM #fix
    SELECT SUM(MBFree) AS SumMBFree FROM #fix.

    satya, May 28, 2008
    #13

  13. Kewin New Member

    There's also another aspect that came to mind.
    All the xp_* procs are on the 'do not use for new development' list, since they're all targeted for deprecation.

    What about writing one's own CLR-proc to replace xp_fixeddrives?

    This is something that's been suggested to MS to provide as replacement, but while we're waiting it may be worth looking into..?

    /Kenneth
    Kewin, May 28, 2008
    #14

  14. satya Moderator

    Oh yes it is better to keep away or depending upon undocumented procedures, this is another example what you will get when it has been working well for ages.
    See relevant topic in http://www.sqlservercentral.com/Forums/Topic113227-5-1.aspx#bm122603 which might another way to accomplish.
    I would also agree to look into CLR procedures to keep it running in future versions.
    satya, May 29, 2008
    #15

  15. MichaelB Member

    [quote user="satya"]
    Hmm, it works for me.
    Try using the following script and create in relevant SQL Server to extract required information using EXECUTE AS method:
    CREATE TABLE #fix
    (Drive NVARCHAR(5),
    MBFree INT)

    INSERT INTO #fix
    EXEC xp_fixeddrives

    SELECT * FROM #fix
    SELECT SUM(MBFree) AS SumMBFree FROM #fix.

    [/quote]
    This does not work for me. I get empty rowsets. I create a SP with execute as CALLER. correct?
    MichaelB, May 29, 2008
    #16

  16. satya Moderator

    Does that CALLEr has relevant privileges on master database to get information for this XP?
    satya, May 30, 2008
    #17

  17. MichaelB Member

    Yup. Caller would reference me, which I do.
    MichaelB, May 30, 2008
    #18

Share This Page