• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

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

0
XGIS
Asked:
XGIS
  • 2
1 Solution
 
computerstreberCommented:
The bulkadmin role is a server. To access from management studio, collapse the node for SECURITY for the server then collapse the node for SERVER ROLES. The BULKADMIN server role is typically the first role in the list, provided you have not created any custom roles. Open the role and click add to select a LOGIN to add to the role's membership.  
0
 
computerstreberCommented:
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

0
 
XGISAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now