Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    3

    Unanswered: Trigger Execution Permissions

    Probably a simple question.

    Do triggers execute using the permissions of the user that caused the trigger to execute?


    Thanks!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No, triggers execute independent of the user permissions.

  3. #3
    Join Date
    Mar 2004
    Posts
    3
    Originally posted by rdjabarov
    No, triggers execute independent of the user permissions.
    What user account will the trigger execute as?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Triggers are a special case of stored procedures, in that they execute implicitly when a table is modified instead of via an explicit call. Stored procedures (and therefore triggers) use the permissions of the creator, not the executor.

    -PatP

  5. #5
    Join Date
    Mar 2004
    Posts
    3
    Originally posted by Pat Phelan
    Triggers are a special case of stored procedures, in that they execute implicitly when a table is modified instead of via an explicit call. Stored procedures (and therefore triggers) use the permissions of the creator, not the executor.

    -PatP
    Thank you very much!

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Good question..

    Permissions
    CREATE TRIGGER permissions default to the table owner on which the trigger is defined, the sysadmin fixed server role, and members of the db_owner and db_ddladmin fixed database roles, and are not transferable.

    To retrieve data from a table or view, a user must have SELECT statement permission on the table or view. To update the content of a table or view, a user must have INSERT, DELETE, and UPDATE statement permissions on the table or view.

    If an INSTEAD OF trigger exists on a view, the user must have INSERT, DELETE, and UPDATE privileges on that view to issue INSERT, DELETE, and UPDATE statements against the view, regardless of whether the execution actually performs such an operation on the view.

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    Triggers are a special case of stored procedures, in that they execute implicitly when a table is modified instead of via an explicit call. Stored procedures (and therefore triggers) use the permissions of the creator, not the executor.

    -PatP
    You sure about that?

    That's what my thinking was....but what about the bol reference from CREATE TRIGGER?

    Got to test it...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm 99 5/8% sure that reference was to the creation of the trigger. I don't think it matters squat to the execution of the trigger.

    -PatP

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Make it 100%...that's what I thought...

    but what's up with my login/ role/user problems?



    Code:
    USE Northwind
    GO
    
    if not exists (select * from master.dbo.syslogins where loginname = N'myLogin99')
    BEGIN
    	DECLARE @logindb nvarchar(132), @loginlang nvarchar(132)
    
    	SELECT @logindb = N'Northwind', @loginlang = N'us_english'
    	IF @logindb IS NULL OR NOT EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE name = @logindb)
    		SELECT @logindb = N'master'
    	IF @loginlang is null or (not exists (SELECT * FROM master.dbo.syslanguages WHERE name = @loginlang) 
    	  and @loginlang <> N'us_english')
    		SELECT @loginlang = @@language
    	EXEC sp_addlogin N'myLogin99', null, @logindb, @loginlang
    END
    
    if not exists (select * from dbo.sysusers where name = N'myRole99' and uid > 16399)
    	EXEC TaxReconDB_Prod..sp_addrole N'myRole99'
    
    EXEC TaxReconDB_Prod..sp_adduser 'myLogin99', 'myLogin99', 'myRole99'
    GO
    
    -- OK bizaro time....what's wrong with above...it say it adds the login, user and role
    -- but then the grnat fails saying it doesn't exist.  Look in EM and it doesn't exists
    -- The Login exists, but no User or Role
    -- Had to add them in manually
    
    
    CREATE TABLE myTable99(Col1 int)
    GO
    
    GRANT SELECT, INSERT, UPDATE, DELETE ON myTable99 TO myRole99
    GO
    
    CREATE TABLE myTable00(Col1 int)
    GO
    
    GRANT SELECT ON myTable00 TO myRole99
    GO
    
    CREATE TRIGGER myTrigger99
    ON myTable99
    FOR INSERT
    AS 
    
    INSERT INTO myTable00(Col1) SELECT Col1 FROM inserted
    GO
    
    
    -- Execute Code as connected by myLogin99
    
    INSERT INTO myTable99(Col1) SELECT 1
    GO
    
    SELECT * FROM myTable99
    GO
    
    SELECT * FROM myTable00
    GO
    
    -- Back to sysadmin
    
    DROP TABLE myTable00
    DROP TABLE myTable99
    GO
    
    EXEC sp_DropUser  'myLogin99'
    EXEC sp_DropRole  'myRole99'
    EXEC sp_DropLogin 'myLogin99'
    GO
    
    -- Now I get 
    -- Login 'myLogin99' is aliased or mapped to a user in one or more database(s). 
    -- Drop the user or alias before dropping the login.
    -- But I can go in and Delete it manually...  
    -- Got to be those damn surrogate keys....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In SQL Server, logins give you access to the server. Users give you access to the database.

    The server checks your authentication, and associates your session (spid) with a login. That is as far as it goes in the process.

    The system administrator or the database owner can grant a given login access to their database. When your spid attempts to access the database, the login is compared against the users in the database to see which of them are applicable. When you attempt to access an object, it does more compares to see if your user or role has a "mother may I".

    The security model is "interesting", but it works pretty well.

    -PatP

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, but when I use t-sql, it says it creates the rolse, logins, user, ect...

    but when I try the grant it says it doesn't exist

    I then add it manually in EM and then it works....
    I'm confused...my natural state...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •