SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Permission Problems


Permission Problems

Author
Message
funkcommander
funkcommander
Say Hey Kid
Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

Group: General Forum Members
Points: 697 Visits: 1
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!



Steve Jones
Steve Jones
SSC Guru
SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)SSC Guru (585K reputation)

Group: Administrators
Points: 585016 Visits: 20897
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
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
funkcommander
funkcommander
Say Hey Kid
Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

Group: General Forum Members
Points: 697 Visits: 1
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search