How to create logs in a stored procedure

Question

I want to put the logs in some stored procedures in our database to monitor the working of stored procedures. I am new to SQL Server 2008. The logs should be created on the production server.

I had tried this link: http://www.codeproject.com/Articles/18469/Creating-Log-file-for-Stored-Procedure

but get the error message:

The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.

Please provide me some needful.

score 0 · Answer 1 · answered Dec 11 '14 at 05:54

0

granting permissions using the master database to the object should do

Use Master    
grant execute on xp_cmdshell to 'user'

answered Dec 11 '14 at 05:54

msgpdev10

31
4

http://stackoverflow.com/questions/2016669/getting-execute-permission-to-xp-cmdshell You may also refer this link. this has been answered previously. – msgpdev10 Dec 11 '14 at 05:56

score 0 · Accepted Answer · answered Dec 11 '14 at 05:57

First of all are you sure you want to log data to text file? May be it will be better to store log into separate table ?

If you want to work with text file:

Look at description xp_cmdshell

The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account.

Check the security rights for this account.

xp_cmdshell can be enabled and disabled by using the Policy-Based Management or by executing sp_configure.

Check you have it enabled.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

You need to create proxy account.

EXEC sp_xp_cmdshell_proxy_account [MyDomain\SQLServerProxy], 'usdcu&34&23'

Add permissions to use this SP:

USE master;
GRANT EXECUTE on xp_cmdshell to Current_user

Here is a more detailed information.

hi @demas, i dont have permission to execute this, is ther any way to track the storeprocedure's working without using xp_cmdshell — Khushi Sharma, Dec 11 '14 at 09:27
you can change the code of sp and store all information you need in the table you will create for this tasks — ceth, Dec 11 '14 at 09:55

score 0 · Answer 3 · edited Jun 20 '20 at 09:12

Using xp_cmdshell for logging is bad for both security and performance. Please delete that codeproject link from your browser and forget you ever saw it. Seriously, it is badness.

If you want to log calls to procs, either:

Set up a table for this (as demas also suggested). You can have a DATETIME field defaulted to GETDATE() or GETUTCDATE(). You can have a field for the Proc Name, a field for parameters. Whatever.

or
Use SQLCLR to create a stored procedure that does a simple File.Write of the info. You can use Impersonation (something xp_cmdshell can't do) to have the security context be that of the person running the proc and not the Log On account of the SQL Server process. This approach is far more efficient and contained than xp_cmdshell, even when not using Impersonation.

or
Do a combination of the log table + SQL CLR [or something else]: You can log to the table for immediate writing. And then set up a SQL Agent job to archive entries over X days old to a file using SQLCLR or some other means. This way the table doesn't grow too big with info that is probably older than you need anyway for researching problems that are currently happening.

i am also feeling that using xp_cmdshell is not a good idea, because i need an administrave permissons also. therefore, can you please tell me how to create a storedprocedure via sqlclr. — Khushi Sharma, Dec 11 '14 at 09:20

How to create logs in a stored procedure

3 Answers3