General discussion


SQL stored procedure execution

By dmillett ·
I have a stored procedure that runs a report which is written to a directory on the server. It ran fine on Windows Server 2000 and SQL Server 2000 sp2 for any user. After upgrading to Windows Server 2003 and SQL Server 2000 sp3 it executes and writes the file only if you are logged into SQL as "SA".

It seems to bomb at the line: "exec master..xp_cmdshell @ cmd,.." When I ran a SQL trace on the procedure I got a "CacheMiss" statement on the next line when the user was not ?SA?.

I executed the stored procedure through Query Analyzer. It ran all the way though, but when it got to the line where it's supposed to write the file, I received the following error when not logged in as ?SA?:

Msg 50001, Level 1, State 50001
xpsql.cpp: Error 997 from GetProxyAccount on line 604

I looked up the ?Msg 50001? error on Microsofts Knowlege Base and found article #833559 about a user not being able to run the xp_cmdshell extended stored procedure. It talks about configuring a proxy account but I have no idea how to do that or what they mean by the "Local Security Authority (LSA) Secrets Database". I?m rather new to SQL Administration.

I have checked rights in SQL and in Windows Server 2003 but have not been able to solve the problem. I feel sure that it?s something involved in the new security for Windows Server 2003 or SQL Server 2000 Service Pk 3.

I need for my users to be able to execute these reports with their own login. Just so you know Grant70 commands do not help. I have checked the permissions on the stored procedure and ?DYNGRP? has EXEC permission. Everyone is a member of that Group.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Related Discussions

Related Forums