April 23, 2018 at 12:46 pm
I need to exec xp_fixeddrives against several remote SQL servers, 2016 and 2014, and everything was fine as long as I was using sa for the linked server connection in my testing environment. But when I tried to switch to some unprivileged login, I started receiving blank results from remoteserver.master.sys.xp_fixeddrives. (i.e. i've got enough access rights to run xp_fixeddrives, but it returns empty result)
Then I tried to execute xp_fixeddrives locally on the same server under the same unprivileged account with the same blank output.
Then I created a stored procedure with execute as owner, (with dbo being the owner), executing xp_fixeddrives, but still no luck
Then I tried adding sysadmin role to my account - and that worked, but that's not something that I need.
Then I thought 'Ok, maybe that's because it's not Windows account and somehow has no access to the filesystem when it's not admin'. And I created another unprivileged login, with Windows credentials this time. I tried executing xp_fixeddrives both locally and remotely - and gotcha!!! Everything was fine and it got my results.
But...
AFAIK, a linked server object can only be created either with non-Windows account (which gets blank results), or 'impersonate as self' feature (which requires me to be logged on my primary server under the remote windows account, which is not possible because the server is in other domain without trust relationship).
Soo... i tried then to make impersonation on the remote server itself. i.e. i have non-windows account 'user1' and Windows account 'domain\user1', both unprivileged, i've executed GRANT IMPERSONATE ON LOGIN::[domain\user1] TO [user1] and i'm now logged on to the remote server itself (and not via linked server object) with 'user1' account. But when I run
execute as login='domain\user1'
exec master.sys.xp_fixeddrives
I still have blank results
Can anyone explain, why if I directly login as unprivileged domain\user1 (with only public role assigned) and run xp_fixeddrives - I get results, both locally and remotely; if I add domain\user1 to sysadmin role and run xp_fixeddrives being logged under user1 account with impersonation to domain\user1 - I get results; but when I'm logged as user1 and run xp_fixeddrives with impersonation to unprivileged domain\user1 account - I get no results?
May be there's still a way to link a server with foreign Windows credentials?
Or maybe there are other (better?) ways of getting free drive space info via linked server object with an account as unprivileged as possible?
April 23, 2018 at 1:17 pm
There are better ways.
This article will work, probably much better than xp_fixeddrives and linked servers
http://www.sqlservercentral.com/articles/Drive+space/134523/
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
April 24, 2018 at 4:14 am
Hm... not the simplest solution, and surely overkill for what I need, but the idea of collecting xp_fixeddrives data in some table looks feasible, I'll try that.
April 24, 2018 at 5:04 am
While trying to implement the 'collect, then select' solution, I found out, that the procedure I mentioned in my post:
CREATE PROCEDURE [dbo].[uxp_fixeddrives]
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
exec sys.xp_fixeddrives
END
works just fine from msdb (but not from master, where I put it earlier)
So, the problem is solved
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply