| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2004-09-17 : 18:14:46
|
| I have a trigger that updates the Contacts table in DatabaseA when the Contacts table in DatabaseB is updated. The user uses an application built for DatabaseB, and the app stores his username and password in a table in DatabaseB. My problem is that I don't know how to give the user permissions on the Contacts table in DatabaseA so that the trigger can execute without error. Is there a way to specify a user inside the trigger? |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-17 : 18:30:42
|
| While I'm still curious about my question, it turns out giving the user NTFS permissions to the table worked, the error I was getting is because of a primary key violation in the Contacts table in DatabaseA. The trigger deletes everything in the table and then inserts everything from DatabaseB.Contacts into it. I guess it must be a timing issue or something, I don't understand it. |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-21 : 15:51:23
|
| It's not working again, so I'm thinking the NTFS permissions weren't being carried through. I was getting a different error before, so I thought the permissions were working, but maybe the other error just too precedence or something. Is there a way to set the trigger or table so that anybody (even unauthenticated) can update it? |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-21 : 23:49:12
|
you can't set a permission for a trigger it's event driven, you can however set the permissions on the tables concerned.you may want to post your error message and trigger script here so that someone here can help you, they will if they understand your problem.  |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-22 : 12:52:18
|
| I get "DELETE permission denied" on the table that's receiving the trigger actions (i.e. not the table that causes the trigger to fire). I tried setting the trigger to fire a local package (dtsrun) that executed the necessary T-SQL but that caused sever locks every time, and left the more important table empty. (It's more important because it's part of a database created by a third party for a multi-thousand-dollar third-party app.) So I'm going back to the triggers and I'm gonna try giving the "Public" user permission to delete, and I'll see what happens. Thanks jen. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 14:37:55
|
| You should be granting permissions through roles and not using public.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-22 : 14:41:51
|
| It didn't work. I even tried adding "guest" permissions, and it still didn't work. Why is there not a checkable EXEC box in the permissions window for tables? Do they need EXEC permissions to run a trigger? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 14:44:29
|
| They need permissions on the tables. Put your users in a role. Grant DELETE to this role for the table inside the trigger.Aren't you using stored procedures for data access?Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-22 : 14:58:40
|
| Okay so I remembered why EXEC is not checkable for a table.Sometimes they use Windows perms, and sometimes SQL Server perms, but I've given all possible users DELETE, INSERT, UPDATE, and SELECT on the table, and they still get an error. The third-party application that accesses the important table does not appear to use stored procedures, and there's nothing I can do about that. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 15:04:00
|
| Right click on the table that is shown in the error. Go to all task..manage permissions. Some user in that list does not have permissions. Maybe deny delete is set for this table.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-22 : 15:52:07
|
| Yup, I've spent quite a bit of time there, and there's no sign of DELETE being denied. I just used SQL profiler and figured out what user is actually being used to run the query. Then I gave that user EXEC permissions on the stored procedure (I moved the trigger's T-SQL into SP's) and full permissions on the tables. It still doesn't work. Did I miss a variable (event class) in Profiler that should tell me why the permissions are failing? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 15:56:33
|
| Add Errors and Warnings:Exception for events. Add Success for Data Columns. You should see Success = 0 when you get the permission problem. You might want to add ObjectName to Data Columns as well.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-22 : 16:11:37
|
| Since it's a bidrectional trigger, the first line in each one (now stored procedure) is:ALTER TABLE DatabaseA..Contacts DISABLE TRIGGER ALLThat way a loop doesn't start. But it appears that that is what is causing the permissions error, unless that's just shown in Profiler because it's the first line of the stored procedure. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-22 : 16:13:04
|
Ah! Well the user would need ALTER TABLE permissions which is highly not recommended.quote:
ALTER TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.
Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-22 : 18:47:10
|
| Yup, that was the problem. It is worth giving CREATE TABLE permissions in this case because security is not much of a concern (having it work is far more important). thanks Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-23 : 15:15:22
|
| I'm sorry to say that I'm still having problems. When I run the third-party app with my NTFS permissions, I can add a contact to the Contacts table and everything works great. But when the people who will actually use the program regularly do it, they get a permissions error (Error: 3704, Severity: 16, State: 1 --in Profiler). I've given the Active Directory group that they are in CREATE TABLE permissions on the db, to no avail. The weird thing is that I can't see any place where it says that I have permissions to disable triggers. Also when I look at the SQL permissions for the AD group in question, for DatabaseA it says DOMAIN\group, but for DatabaseB it just says the group name. Could that make a difference? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-23 : 15:18:34
|
| CREATE TABLE permissions is not enough for this. See my last post for what I quoted from SQL Server Books Online.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-23 : 15:33:16
|
| Now I get it, kind of. I just found the spot where I have permissions (I'm part of the Systems Administrator role). What does "not transferable" mean? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-23 : 15:35:32
|
| It means you can't give explicit ALTER TABLE permissions to anyone. You should really rethink your design though. The security you are about to implement just because of this trigger is against all security best practices.Tara |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-09-23 : 15:41:43
|
| You're right, I know, but it's a small, private database, and they wouldn't even know how to CREATE something even if they knew they were able to. If I make them part of the db_ddladmin role, will they then be able to run ALTER TABLE on the table even if they didn't create the table? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-23 : 15:44:09
|
| Yes they would. If they've got Enterprise Manager, I wouldn't underestimate what they would do. Even if they don't know how to do it doesn't mean they won't make a mistake when clicking around.Tara |
|
|
|
Next Page
|
trigger permissions