XP_FIXEDDRIVES Error/Missing MB Free Column when trying to perform a MSSMS Backup

Question

XP_FIXEDDRIVES Error/Missing MB Free Column when trying to perform a MSSMS Backup

pmal

Old Hand

Points: 364
More actions
March 26, 2014 at 1:39 pm

#305652

Hi,
Can someone move this to the Administration section? I'm sorry I posted in the wrong place.
I'm getting an error in SQL Management Studio like the site attached . All my sql service account permissions seem to be good. I get this whether I run them as a local admin user, a domain admin user that has local admin rights, or sa user (when the sql service account is set to use all of the above, except for sa of course) and I've verified the folder security for each in Windows. When I look at the details of the error message when trying to add a backup device/restore location in SQL Studio, it says the following abbreviated:
Column name or number of supplied values does not match table definition. (.Net SqlClient Data Provider)
Error Number: 213
Severity: 16
State: 7
Procedure: xp_fixeddrives
Line Number: 1
If I run this "EXEC MASTER..xp_fixeddrives", I only get one column, "drive", with two rows "C" & "D" as it should but the results are missing the "MB Free" column. If I run on a working machine, I get both columns, "drive" and "MB Free". Any ideas on what might be causing this?
FYI, I can run restores and backups fine if I run them using t-sql scripts so I'm not dead in the water but I would like the GUI to work.
Thanks, Phillip

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 1

pmal (3/26/2014)
Hi,
Can someone move this to the Administration section? I'm sorry I posted in the wrong place.
I'm getting an error in SQL Management Studio like the site attached . All my sql service account permissions seem to be good. I get this whether I run them as a local admin user, a domain admin user that has local admin rights, or sa user (when the sql service account is set to use all of the above, except for sa of course) and I've verified the folder security for each in Windows. When I look at the details of the error message when trying to add a backup device/restore location in SQL Studio, it says the following abbreviated:
Column name or number of supplied values does not match table definition. (.Net SqlClient Data Provider)
Error Number: 213
Severity: 16
State: 7
Procedure: xp_fixeddrives
Line Number: 1
If I run this "EXEC MASTER..xp_fixeddrives", I only get one column, "drive", with two rows "C" & "D" as it should but the results are missing the "MB Free" column. If I run on a working machine, I get both columns, "drive" and "MB Free". Any ideas on what might be causing this?
FYI, I can run restores and backups fine if I run them using t-sql scripts so I'm not dead in the water but I would like the GUI to work.
Thanks, Phillip

Does the account under which SQL Server runs have permissions to the drives?

thanks

John.

pmal Old Hand Points: 364 More actions · Answer 2

JohnFTamburo (3/26/2014)
Does the account under which SQL Server runs have permissions to the drives?
thanks
John.

I've looked at that from all angles and it's almost like the accounts don't have rights, even though both drives' security section shows that it does. I've tried this with the LocalSystem Account and I've tried it with a domain user with admin rights on the domain and on the local computer, where I went in and added that user to both hard drives and set it to have full rights. It's crazy.

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 3

pmal (3/26/2014)
JohnFTamburo (3/26/2014)
Does the account under which SQL Server runs have permissions to the drives?
thanks
John.
I've looked at that from all angles and it's almost like the accounts don't have rights, even though both drives' security section shows that it does. I've tried this with the LocalSystem Account and I've tried it with a domain user with admin rights on the domain and on the local computer, where I went in and added that user to both hard drives and set it to have full rights. It's crazy.

Hi. Please go to admin tools and choose services and see what account the SQL Server service is running. Remember that SQL Server does not impersonate the rights of the login that opens the session; it always uses the rights that the service account has.

Thanks

John.

pmal Old Hand Points: 364 More actions · Answer 4

It's currently using the domain account with domain admin rights that I mentioned earlier. I also just doubled checked to see if Domain Admins was setup in the local admin group and it is.

Thanks, Phillip

SQLBlimp SSCertifiable Points: 6340 More actions · Answer 5

pmal (3/26/2014)
It's currently using the domain account with domain admin rights that I mentioned earlier. I also just doubled checked to see if Domain Admins was setup in the local admin group and it is.
Thanks, Phillip

Was the user profile in the service entry changed? If so was SQL Server restarted after the change?

Can you try using xp_cmdshell and doing a DIR of the folder you want to access to see what comes back?

Thanks

John.

pmal Old Hand Points: 364 More actions · Answer 6

Just making sure I understand the first question, I did do a restart after I changed the service account to the domain account. If I create a batch file that runs a script and then run that with Task Scheduler tied to the same domain account as the SQL Server Service Account, and have my script run a directory listing to the backup folder using xp_cmdshell, I get a dump of the C:\windows\system32 folder listing, as I would expect. It's very peculiar. It's like sql and/or the windows 2008 r2 doesn't recognize the rights my account has. I have already tried a reinstall.