Msg 229 … EXECUTE permission was denied on the object ‘xp_cmdshell’

\ by


–xp_cmdshell 'dir e:\mssql\reports'
–Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
–The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

EXEC sp__cmdshell  'dir e:\mssql\reports'

Works!

Here's the procedure:

CREATE PROCEDURE [dbo].[sp__cmdshell] (

   @vcCmd VARCHAR(8000),

   @bNoOutput bit = 0

)

AS

SET nocount ON

DECLARE @RC INT

IF @bNoOutput = 0

   EXEC @RC = master.dbo.xp_cmdshell @vcCmd

ELSE

   EXEC @RC = master.dbo.xp_cmdshell @vcCmd, no_output

IF @RC <> 0 OR @@ERROR <> 0

   PRINT 'ERROR: ' + @vcCmd

RETURN @RC

GO

You probably also need to give permissions to a login or a role to run the new procedure.

GRANT EXECUTE ON [dbo].[sp__cmdshell] TO [db_executor]

Posted in: SQL

Create a linked server to a local directory path

\ by

Here's what I did to successfully set up a linked server to a directory on a SQL 2000 or SQL 2005 server.


/* Remove any previous references to the linked server */
USE [master]
GO

/****** Object:  LinkedServer [LOCAL_PATH]    Script Date: 01/06/2009 14:04:46 ******/
EXEC master.dbo.sp_dropserver @server=N'LOCAL_PATH', @droplogins='droplogins'
GO

EXEC sp_addlinkedserver 'LOCAL_PATH',
    'Jet 4.0',
    'Microsoft.Jet.OLEDB.4.0',
    'E:\MSSQL\Reports', NULL, 'Text'

GO

EXEC sp_tables_ex
   @table_server='LOCAL_PATH'

—
— If you get this error
—
—
–OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH" returned message "Unspecified error".
–Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
–Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LOCAL_PATH".

–Simply log onto the server as the same account that the sql services run as.
–Then under my computer properties changes the TEMP and TMP environment variables to something like
    C:\temp
    or
    C:\TempSQL
–which should have security permissions changed to allow local users to have RWC permissions.

 You might also lookup this on the Microsoft site.
    PRB: Error 7399 When You Run a Linked Server Query That Uses the OLE DB Provider for Microsoft Jet

 

Posted in: SQL Server