August 22, 2002 at 10:19 am
I have a vb script that is called from an ASP page that:
1. Uses the isql command to call a .sql file. This file uses the BULK INSERT command
2. Uses the bcp command to export the results to an .xls file
Initially I used the -E option for a trusted connection. When executing this isql command from the cmd line, it works. When I incorporated it into the web form, it failed for ANONYMOUS LOGON
Msg 18456, Level 14, State 1:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
DB-Library: Login incorrect.
So I created a userid "Web" and password "Web"
Here's the isql command:
****************************
isql -S HYPERION -d rfq -Uweb -Pweb -i E:\inetutility\ssbom.sql -o E:\inetutility\log.txt
********************************
I then get the following error:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> (0 rows affected)
Msg 4834, Level 16, State 1, Server HYPERION, Line 3
You do not have permission to use the BULK INSERT statement.
So My main question is what do I have to do to give this new user the correct rights? I have given it ALL DB membership roles, and have gone into the permissions and selected the correct tables. Am I missing something here. I would think with everything selected, they should have the correct roles. Is the problem now in my .sql script?
Here's the contents of the .sql file
********************************
delete from ssbom;
BULK INSERT ssbom
FROM 'E:\inetutility\ssbom.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
MAXERRORS = 10
)
*****************************
Once I get past this part, the bcp command should work.
Thanks
Petey
August 22, 2002 at 10:40 am
Is the users a member of the sysadmin or bulkadmin role? It is my understanding that the user needs to be in one of these roles to execute BULK INSERT. Also if you are exporting the user will also need SELECT access to the table being exported. For imports they will need SELECT and INSERT permissions to the table being imported.
-------------------------
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 22, 2002 at 10:55 am
sysadmin did it! Thanks
August 22, 2002 at 11:21 am
Are you really sure you want to give a web user SYSADMIN permissions? I think I would have picked BULKADMIN role. I don't put anyone in SYSADMIN except DBA types.
Gee what did you say the IP address was for server HYPERION? Just kidding don't send it!
-------------------------
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
August 22, 2002 at 1:34 pm
If it's not SQL 2K, no bulkadmin role.
If you are SQL 2K, I agree with GAL0303. Only give the permissions sufficient to do the job and no more. Also, make sure the user account has the ability to insert into the table. The bulkadmin role gives the ability to use BULK INSERT, but the user still needs rights for the table in question to do so.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply