RE: Bulkload account permissions
- From: Bob <Bob@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 4 Aug 2009 15:36:01 -0700
Yes you will have to do this on a per table basis as indicated in my example
code.
You could also add the users to the db_datareader and db_datawriter database
roles, but as you wanted to be more controlled with your security, the table
by table basis is the way to go.
"Kurt Bauer" wrote:
Thanks for the feedback, but I'm still not getting it working. Do I have to.
give permissions on each table? I've been creating and giving permissions at
the database level. Below is an example of the script I use to create the
account. When I run the BulkImport, I receive the following error: "CREATE
TABLE permission denied in database 'target'."
Script to create account:
CREATE LOGIN [myuser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[TARGET],
DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
CREATE USER myuser FOR LOGIN myuser WITH DEFAULT_SCHEMA = TARGET;
GO
GRANT SELECT TO [myuser]
GRANT INSERT TO [myuser]
GO
"Bob" wrote:
Sorry, my mistake, bulkadmin role is for BULK INSERT operation.
I got a SQLXMLBulkLoad to work by granting only SELECT and INSERT
permissions on the target table, eg
GRANT SELECT, INSERT ON dbo.target TO user1
"Bob" wrote:
As a minimum, your user will need to be in the bulkadmin role and have INSERT
permission on the table, eg
EXEC sys.sp_addsrvrolemember @loginame = N'user1', @rolename = N'bulkadmin'
GRANT INSERT ON dbo.target TO user1
HTH
wBob
"Kurt Bauer" wrote:
I am using SQLXMLBulkLoad 4.0 to import XML files into a SQL database. I'd
like to use a specific user id and password for the connection string. For
security reasons, I'd also like to specify explicit permissions on the
account, but I haven't been able to find the correct combination of
permissions for the account to successfully execute the bulkload unless the
account is db_owner.
What permissions are required for bulkload to work correctly, or do I just
need to give the account db_owner permissions to the database?
Thanks,
Kurt Bauer
- References:
- RE: Bulkload account permissions
- From: Kurt Bauer
- RE: Bulkload account permissions
- Prev by Date: RE: need a XMLBULKLOAD example that fits my data,
- Next by Date: Re: OpenXML() Edge Table
- Previous by thread: RE: Bulkload account permissions
- Next by thread: need a XMLBULKLOAD example that fits my data,
- Index(es):
Relevant Pages
|
