Permissions

Question

Permissions

SGT_squeequal

SSCertifiable

Points: 7167
More actions
April 28, 2017 at 7:59 am

#324904

HI
im having a few issues trying to set minimum permissions, for any table that has a trigger i can not update or insert even though i have update and insert permissions on both tables. if i disable the trigger all is good but if i re enable i get a permissions error (the user does not have permissions) the trigger just inserts data from table one into table 2 from deleted.
***The first step is always the hardest *******

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

My guess would be that the user trying to INSERT/UPDATE doesn't have permission (or has DENY) on the table the trigger is inserting into. is the table that the trigger it is inserting into on a different database, or does it have a different owner?

What is the error message you are receiving when you attempt to insert/update?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

When i disable the trigger i can insert or update both tables no problems when the trigger is on i get this error the The user does not have permission to perform this action

***The first step is always the hardest *******

Lowell SSC Guru Points: 323504 More actions · Answer 3

the trigger is probably inserting into an audit table, right? and a normal end user does not have access to that table; it might be sending an email, or something else, but the code INSIDE the trigger is doing something the normal user does not have permissions for.

I would recommend either signing the trigger with a certificate, or changing the trigger to EXECUTE AS OWNER to resolve the permissions issue.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

Thanks lowell
the trigger fires after insert or update and is as basic as; insert x from inserted or deleted. I have select, update, insert and delete permissions on both tables and I can insert update and delete from both tables but I can not insert when trigger is enabled.

I can get this to work if i assign myself view server state but i am trying to avoid using this principle

***The first step is always the hardest *******

Ed Wagner SSC Guru Points: 287024 More actions · Answer 5

SGT_squeequal - Sunday, April 30, 2017 2:58 AM
Thanks lowell
the trigger fires after insert or update and is as basic as; insert x from inserted or deleted. I have select, update, insert and delete permissions on both tables and I can insert update and delete from both tables but I can not insert when trigger is enabled.
I can get this to work if i assign myself view server state but i am trying to avoid using this principle

If the trigger is inserting into an audit table, you probably don't want to grant SELECT, UPDATE and DELETE permissions on the audit table to everyone. PLEASE don't grant view server state to a limited login. You're doing it right by going with least privs, so don't start granting the keys to the kingdom.

Did you change the trigger the way Lowell suggested - WITH EXECUTE AS OWNER in the definition of the trigger?

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Lowell SSC Guru Points: 323504 More actions · Answer 6

view server state? i guess you are trying to select info about the connection from sys.dm_exec_connections

stick with what i said, either sign the trigger or use EXECUTE AS OWNER

there are ConnectionProperty functions that you can sue to get the same info that exists in the dmv's without access.
here's decent example of an audit that works in a trigger:
SELECT getdate() AS EventDate, DB_NAME() AS DBName, CURRENT_USER AS CurrentUser, HOST_NAME() AS HostName, APP_NAME() AS ApplicationName, OBJECT_NAME(@@PROCID) AS ProcedureName, USER_ID() AS Userid, USER_NAME() AS UserName, SUSER_ID() AS sUserid, SUSER_SNAME() AS sUserName, IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin], IS_MEMBER('db_owner') AS [Is_DB_owner], IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin], IS_MEMBER('db_datareader') AS [Is_DB_Datareader], ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN], ConnectionProperty('net_transport') AS 'net_transport', ConnectionProperty('protocol_type') AS 'protocol_type', ConnectionProperty('auth_scheme') AS 'auth_scheme', ConnectionProperty('local_net_address') AS 'local_net_address', ConnectionProperty('local_tcp_port') AS 'local_tcp_port', ConnectionProperty('client_net_address') AS 'client_net_address', ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!