asked on

Fix SQL 2005 Bulk Load from Textfile Security Issue

I am trying to upload a list of Users in Roles to a remote shared SQL 2005 server. I have my database and want to upload a TAB delimited text file of 'UserId' and 'RoleId' to APPEND to existing records.

My Error is "You do not have permission to use the bulk load statement."

How do I modify security for my role, or the database to gain permission to perform the task without being 'sysadmin', and if you advise bulkadmin where is it, as i have seen it as a solution but am unable to locate it in SSMSE2005.

Will the script below suffice or does it need more syntax or field declarations?

BULK INSERT aspnet_UIR
FROM 'C:\UIR.txt'
GO

Open in new window

ASKER CERTIFIED SOLUTION

computerstreber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

computerstreber

Sorry... first line should read: The BULKADMIN role is a server role. You can also use the following query to add logins to the role:

EXEC master..sp_addsrvrolemember @loginame = N'loginname', @rolename = N'bulkadmin'
GO

Open in new window

XGIS

ASKER

Since I wasnt a sysadmin i couldnt run the script or change the permissions. I backed up and migrated the database from 2005 and restored to my own 2008 and then permission change worked fine. I then Exported the table as sql to the following syntax;

INSERT INTO [dbo].[aspnet_UsersInRoles] ([UserId], [RoleId])
VALUES (N'{1EA9F953-8594-424F-B092-21CE0ED125E2}', N'{BF3ED7AA-93ED-4019-A301-47F6480907AB}')
GO

and generated the remaiing rows to be appended in excel. Then ran the entire script, with no trigger issues. Task Complete.
A bit primitive but it worked.

Thankyou for your assistance.