What roles are required to import data using bcp utilitygreenspun.com : LUSENET : SQL Server Database Administration : One Thread
Hi When a user with db_dataread and db_datawrite roles imports into tables with identity columns, I get a 'Not a db owner' error message. Can u help me with the same? I am using a temp user who will be granted these roles to import, import the data and then drop the user. Any help would be appreciated. Thanks, Srilatha
-- Anonymous, January 20, 2000
The SQL Server Books Online article, "Copying Data from a Data File to SQL Server" states:
Be sure that the user account used to log in to SQL Server using bcp (or the query tool when using the BULK INSERT statement) has SELECT and INSERT permissions on the table (assigned by the table owner). Note Only members of the sysadmin fixed server role can execute the BULK INSERT statement.
Hope this helps,
-- Anonymous, January 23, 2000
With bcp, you can specify that you want to keep the identity values (from the file) with -E. This is similar to doing SET IDENTITY_INSERT tablename ON in an ordinary app (non-BULK INSERT). This may eliminate the need to grant db_owner.
-- Anonymous, January 30, 2000