Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
 Posted - 2006-05-25 : 17:08:05
|
I have a web application that connects to our db on an application account.This account has been set up to allow bulk insert statements to run.However I do not want to grant the Application account db_owner permissions.Is their a way to configure the account to run bulk inserts without granting db_owner permissions? You can do anything at www.zombo.com |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-25 : 17:12:17
|
From SQL Server Books Online:quote:
PermissionsOnly members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
Tara Kizeraka tduggan |
 |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2006-05-25 : 17:17:12
|
| Yes you are Correct and I have the account working but it if I remove the db_owner permissions and only assign execute permissions to the sp coupled with buladmin I get a message that the account needs db_owner permissions (which I do not wish to grant).Is their a way to configure security to resolve the db owner permissionsYou can do anything at www.zombo.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-25 : 17:20:37
|
| So did you add the account to the bulkadmin fixed server role? This role can be found in EM under Security..Server Roles. Name of it is Bulk Insert Administrators.Tara Kizeraka tduggan |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2006-05-25 : 17:25:00
|
| Yes, the account is configured and it works with the following fixed roles Bulk Insert Administratorsdb_ownerI do not want to grant db_owner.You can do anything at www.zombo.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-25 : 17:26:00
|
| I get now what your problem is. You are trying to allow the account to bulk insert into a dbo object. That isn't allowed. It will be able to bulk insert into an object that it is the owner of. So if your object really needs to be dbo and the account really needs to be able to bulk insert into it, then you must grant db_owner to it.Tara Kizeraka tduggan |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2006-05-25 : 20:00:22
|
| Ahh... thank you TaraYou can do anything at www.zombo.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-05-26 : 11:50:33
|
| An alternative solution would be to create an object owned by this user that is identical to the dbo object. Have this user BULK INSERT into this other table, then INSERT the rows to the dbo object from the other table. This would require INSERT privileges on the dbo object. It does include an extra step, an extra object, and an extra permission, but at least you won't have to give out db_owner.Tara Kizeraka tduggan |
|
|
|
|
|
|
Security - Bulk Insert