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 |
|
sqllearner
Aged Yak Warrior
639 Posts |
 Posted - 2007-03-12 : 22:34:41
|
| How can I give the access to a user "emp_load_app" to truncate and insert data only to tbl_emp table of emp_load database.This user should not have any other access.Is it possible. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-12 : 22:38:36
|
| To TRUNCATE a table your user needs to be sysadmin, db_owner or db_ddladmin - so that's going to be tricky!If DELETE would be enough then you can do:GRANT INSERT, DELETE on [tbl_emp] TO MyUserand avoid granting any permissions on the other tables.Kristen |
 |
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2007-03-12 : 22:42:23
|
| No it should be truncate only ,Can we create some role and use it or can we create job and this job should be called by that user. |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2007-03-12 : 23:02:54
|
| I tried assigning the user to the role and created a stored procedure which will truncate the table.The role will have permission to execute the Stored procedure.But its still giving me error :Server: Msg 3704, Level 16, State 1, Procedure usp_test, Line 4User does not have permission to perform this operation on table tbl_emp |
|
|
|
sqllearner
Aged Yak Warrior
639 Posts |
Posted - 2007-03-13 : 15:13:57
|
| Have any one did this before :Basically a user should be able to select/insert/Truncate and no other option like changing the schema or accessing any other tables.Please let me know |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-13 : 15:32:32
|
| Don't you trust Kristen?Peter LarssonHelsingborg, Sweden |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-03-13 : 16:07:07
|
| Since you posted in the forum, I assume you aren't able to use 2005. If that is the case, Kristen is correct and you are going to have make a choice.However, if you do have access to 2005, check out this article:[url]http://builder.com.com/5100-6388_14-6158511.html[/url]-Ryan |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-14 : 05:08:58
|
| That looks handyI wonder if I can use that to stamp out the last few vestiges of SELECT permissions on tables - because of dynamic SQL calls [from a User Defined SProc] to sp_ExecuteSQL ?Kristen |
|
|
|
|
|
|
|
|
Truncate options