Wednesday, February 3, 2010

Error when using linked server

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4266&EvtSrc=MSSQLServer&EvtID=229&LinkId=20476

FIX
GRANT EXECUTE ON xp_prop_oledb_provider TO [UserDomain\Account Name];


Error when using dtutil to deploy SSIS packages

BIDS Helper is deploying packages...

Deploying to SQL Server MSDB on server: NAMEOFSERVER

Error : BIDS Helper encountered an error when deploying package PACKAGENAME.dtsx!

"c:\Program Files\Microsoft SQL Server\90\DTS\binn\dtutil.exe" /FILE "PATHTOPACKAGE.dtsx" /DestServer NAMEOFSERVER /COPY SQL;"Package" /Q

exit code = 6

Microsoft (R) SQL Server SSIS Package Utilities
Version 9.00.4035.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2004. All rights reserved.

Could not save package "PACKAGE" because of error 0x80004005.
Description: Cannot open database "msdb" requested by the login. The login failed.
Source: Microsoft SQL Native Client



Had to give the user's account access to msdb and the dts_admin and dts_operator roles.