September 25, 2001 at 10:33 am
I'm hoping someone out there can lend some assistance. I've been beating my head against a wall for a few days and have pretty much given up. Here's the deal... I have a stored procedure that calls the following line of code:
execute master..xp_cmdshell 'bcp "select * from intranet..btprintqueue" queryout d:\sqlbackup\printfile.dd -T -c -t,'If I run this sp from the query analyzer I get the following output:
SQLState = 42000, NativeError = 229
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'btprintqueue', database 'intranet', owner 'dbo'.
I can't seem to figure out where the permissions come from. BCP is run from a command line, so how do I set permissions so that it can read this table and others? This was working fine just 5 days ago. Someone has done something since then and I can't seem to figure out what it was.
Please help!
September 25, 2001 at 11:15 am
xp_cmdshell - takes rights of the SQL Server service. This means that the command is executed as if the SQL Server Service account logged into the server, opened a command prompt and ran the command.
BCP takes the permissions of the logged in user (with NT auth).
So, if this SQL Server service is running under local system this may not run. I'd setup a SQL user and include that in the command, or change the SQL Server service to run under an account.
You can check by logging into the computer as the SQL Server service and running this command.
Steve Jones
September 25, 2001 at 11:29 am
I thought so and also thought I had already set things up correctly. Someone had changed the login for the NT account for SQL to use but did not change the password. I am not sure how SQL server was running, except that maybe it hadn't been stopped since then. When I stopped and restarted, it was unable to start again. I had already deleted the account for "administrator", or old NT server login, so SQL had no rights until it was stopped and restarted with the correct login.
Thanks for your help!
Shawn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply