Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql server trigger to modify permissions to add records to a table?

Posted on 2006-06-23
16
Medium Priority
?
257 Views
Last Modified: 2012-06-27
is it possible to modify the permissions for a table (allow them to add records when they can't cuz they belong to a group that prohibits) to 2 individuals via a trigger? it only affects one table and should happen when they add records. tia!
0
Comment
Question by:ironpen45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
16 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16969136
the trigger cannot modify permissions, and anyhow, the trigger will not fire if permissions are not set to allow to add records to the table anyhow.

so, to allow insert on a table, you have to go another way:
* use a stored procedure that does the insert
* create a view to which you can grant the insert

0
 

Author Comment

by:ironpen45
ID: 16969310
how can an sp execute the insertion? can an sp override restrictions on a table? doesn't the same apply to a view as well?
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16969372
If I understand you correcetly, what angel is saying is you assign can permissions to the stored procedure for doing inserts. If a person doesn't have permission to run the procedure for the insert.

OR You can use a view to grant permissions to some people but not others for the insert.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16969378
> how can an sp execute the insertion?
>can an sp override restrictions on a table?

Sample code:
CREATE TABLE dbo.test_table ( data varchar(20))
GO
CREATE PROCEDURE dbo.test_proc ( @data varchar(20))
AS INSERT INTO dbo.test_table (data) select @data
GO

now, grant execute permissions to the user for that procedure, not NO permissions on the table.
confirm that the user cannot insert into the table directly.
let the user run the procedure with a parameter, confirm that after the procedure run, the record got inserted to the table

The explanation:
* the procedure owner (dbo) needs to have the permissions to insert to the table,
* the executor or the procedure does NOT need the permissions to the table, only to execute the procedure.

0
 

Author Comment

by:ironpen45
ID: 16969641
i c. so, i have an msa mdb front end. the form's record source is the table in question, and i'm 1 of 2 end users who need to enter records to the table via the form.

how can i make this happen? eg, does the record source have to change? on do i need a command button to execute the sp?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16969782
I know that one can connect with MS Access directly to a sql server database, and have done so for some small configuration tables already, but I don't really like this for multi-user / prod applications, at least not with simple-bound forms.

ie, you would have to have the form unbound, and control all the data display and data save operations manually (by code), using the stored procedures.
0
 

Author Comment

by:ironpen45
ID: 16970697
r u saying that from the unbound form, with the new data in the corresponding controls, execute the sp that will enter the new values into the restricted table? i gather that from ur example, unless i'm misunderstanding u. thanks.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16972231
yes, you got that correct!
0
 

Author Comment

by:ironpen45
ID: 16972246
i just thought of an alternative, a bit simpler than an sp:

i can modify the table for full access to all users, but then a trigger will test whether someone is authorized to enter a record into the table. if the end user is not authorized, the entry is rejected.

can anyone help me with a sample trigger?
0
 

Author Comment

by:ironpen45
ID: 16972255
how about it, angelIII? for 300 pts?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16972296
create trigger trg_check_permissions
  on yourtable
  for update
as
  IF ( <check permissions of user are not good> )
  BEGIN
     ROLLBACK
     RAISERROR (16,1,'You do not have good permissions' )
  END
GO


hope this helps
0
 

Author Comment

by:ironpen45
ID: 16972495
so my trigger would look like this?:

****************
create trigger trg_check_permissions
  on PtblVendor
  for insert
as
  IF ( user='hhenke' or user='kgrecco' or user='psangabriel' or user='mjperry' )
  BEGIN
     ROLLBACK
     RAISERROR (16,1,'You do not have good permissions' )
  END
GO
****************
thanks!
0
 

Author Comment

by:ironpen45
ID: 16972504
actually it's allow the 4 users!:

****************
create trigger trg_check_permissions
  on PtblVendor
  for insert
as
  IF ( user<>'hhenke' or user<>'kgrecco' or user<>'psangabriel' or user<>'mjperry' )
  BEGIN
     ROLLBACK
     RAISERROR (16,1,'You do not have good permissions' )
  END
GO
****************
thanks!
0
 

Author Comment

by:ironpen45
ID: 16972520
i tested it an it's giving me an error 'You do not have good permissions'
0
 

Author Comment

by:ironpen45
ID: 16972536
oops, it's:

Error 170: Line 8: Incorrect syntax near 'You do not have good permissions'
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1300 total points
ID: 16972788
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_5ooi.asp

sorry, i reversed the order of the arguments:

     RAISERROR ('You do not have good permissions' ,16,1)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question