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 |
|
kaus
Posting Yak Master
179 Posts |
 Posted - 2002-02-21 : 19:18:06
|
| I have an access project connecting to SQL Server 2000 using SQL server login account. The project runs a stored procedure that checks if a table exists, drops it if it does and selects data into it. The problem I'm having is that I'm getting an error on the Access side "User does not have permission to perform this operation on table XXXX". I've granted execute permission on the stored procedure for the user and full permissions on the table in question for the user as well. Any ideas what the problem could be. If I use Windows Authentication it works for users with ADMIN privleges only -- otherwise there is an errorThanksPete |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-21 : 19:49:21
|
| Is your user login the table owner or the db_owner?From BOL:DROP TABLE permissions default to the table owner, and are not transferable. However, members of the sysadmin fixed server role or the db_owner and db_dlladmin fixed database roles can drop any object by specifying the owner in the DROP TABLE statement.------------------------GENERAL-ly speaking...Edited by - AjarnMark on 02/21/2002 19:50:13 |
 |
|
|
kaus
Posting Yak Master
179 Posts |
Posted - 2002-02-21 : 19:52:51
|
| So this would mean I would need to grant db_owner role to this login ??ThanksPete |
|
|
|
kaus
Posting Yak Master
179 Posts |
Posted - 2002-02-21 : 20:29:56
|
| I was able to get this to work by granting the user db_owner role on this particular database. It doesnt matter much for this database -- nothing critical etc .. Seems like there should be a better solution than this though -- I'll keep lookingPete |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-22 : 19:41:52
|
| If you're always dropping the table if it exists, and then recreating it, why not use a Temporary table instead?------------------------GENERAL-ly speaking... |
|
|
|
|
|
|
drop table permissions through stored procedure