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

LVL 7
XGISAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.