Post reply

granting insert access for everyone to a table

  • June 26, 2013 at 8:45 am

    #276657

    Ok I created a database AuditDB, and in it one table called ServerLogonHistory.

    Then I create a logon trigger like so:

    use master

    go

    CREATE TRIGGER [Tr_ServerLogon]

    ON ALL SERVER FOR LOGON

    AS

    BEGIN

    if

    ( (ORIGINAL_LOGIN() <> 'sa')

    and APP_NAME() not like 'SQLAgent%'

    and APP_NAME() not like '%IntelliSense'

    )

    INSERT INTO AuditDb.dbo.ServerLogonHistory

    SELECT SYSTEM_USER,USER,APP_NAME(),@@SPID,GETDATE(),HOST_NAME()

    END

    GO

    Now how can I grant every user access to this table so they can logon? Is there a grant insert on <tablename> to public in MSSQL? If not, how is it best done?

    thanks

  • June 26, 2013 at 8:50 am

    #1627602

    yep that's exactly correct. you end up doing a GRANT INSERT ON dbo.ServerLogonHistory TO public in the AuditDb; that way your trigger does not run into permissions issues when non sysadmins login.

    That's exactly how i've done it myself.

    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!

Viewing 2 posts - 1 through 1 (of 1 total)

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