Post reply

SQL Server side trigger

  • September 27, 2012 at 7:11 am

    #268253

    hi friends,

    I need to create a server trigger to automatically map a login to a new database and make it a member of the db_owner database role.

    I was trying the logon trigger but having some tough time with it.

    Please can anyone help me with it

    Thanks

    T

  • September 27, 2012 at 9:32 am

    #1542969

    tauseef.jan (9/27/2012)

    hi friends,

    I need to create a server trigger to automatically map a login to a new database and make it a member of the db_owner database role.

    I was trying the logon trigger but having some tough time with it.

    Please can anyone help me with it

    Thanks

    T

    Not sure I quite understand this one? Are you trying to dynamically add permissions when a specified user logs in?

  • September 27, 2012 at 9:43 am

    #1542977

    I suspect what you want here is a DDL trigger on create database, not a login trigger.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 27, 2012 at 9:50 am

    #1542981

    tauseef.jan (9/27/2012)

    hi friends,

    I need to create a server trigger to automatically map a login to a new database and make it a member of the db_owner database role.

    I was trying the logon trigger but having some tough time with it.

    Please can anyone help me with it

    Thanks

    T

    Two ways i can think of that are a little easire;

    1. add the user to teh model database, so any database that is created via CREATE DATABASE command automatically inherits it.

    2. create a sceduled job that checks each database each day, and adds the user if it's not there.

    now, if you really want a trigger, it's going to require dynamic SQL and elevated permissions(because of the dynamic sql), so I think have a rough example sketched out.

    the problem with that trigger is it cannot catch the AUDIT_BACKUP_RESTORE_EVENT event, i beleve; for that you want to use event notifications.

    if i restore a database form another server, or previous backup, would you want that special user auto-magically added to that one as well?

    this is a snippet adding a specific user;

    it's untested , but a modification of a snippet i already had and tested once.

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTER TRIGGER [SERVER_TR_ADD_ClientAdminX_TO_NEW_DBS]

    ON ALL SERVER

    FOR CREATE_DATABASE

    AS

    declare @eventData XML;

    declare @dbname varchar(100);

    set @eventData = EVENTDATA()

    SELECT @dbname = @eventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME')

    --Dynamic SQL so we can get the context?

    DECLARE @sql VARCHAR(max)

    PRINT 'dbname ' + ISNULL(@dbname,'whoops!')

    SET @sql='

    USE [THEDBNAME];

    PRINT db_name() + '': ADDING User ClientXAdmin ''

    IF EXISTS(SELECT name FROM master.sys.server_principals WHERE name = N''ClientXAdmin'' AND type = ''S'') --''S'' = SQL login

    AND NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''ClientXAdmin'' AND type = ''S'')

    CREATE USER [ClientXAdmin] FOR LOGIN [ClientXAdmin];

    EXEC sp_addrolemember N''AlmostOwners'', N''ClientXAdmin'' --which role(s) should he be added to?

    '

    SET @sql = REPLACE(@sql,'[THEDBNAME]',@dbname)

    PRINT '@sql = ' + ISNULL(@sql,' double whoops!')

    EXECUTE(@sql)

    GO

    SET ANSI_NULLS OFF

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    ENABLE TRIGGER [SERVER_TR_ADD_ClientAdminX_TO_NEW_DBS] ON ALL SERVER

    GO

    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!

  • September 28, 2012 at 12:13 am

    #1543199

    Lowel,

    That looks interesting, I'm looking for something similar except based on a restore of the database, the reason is that we have a partially locked down Dev box where only a select few have admin rights in order to mimic client servers.

    Would this work for Windows Authenticated users?

    Which event would I need to trap for a Restore?

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • September 28, 2012 at 4:27 am

    #1543308

    Thanks guys, especially Lowell for the code snippet.

    Actually I need, when a user creates/restores a database on the server

    he should be automatically be assigned to db_owner role.

    Thanks

    T

  • September 28, 2012 at 7:15 am

    #1543400

    tauseef.jan (9/28/2012)

    Thanks guys, especially Lowell for the code snippet.

    Actually I need, when a user creates/restores a database on the server

    he should be automatically be assigned to db_owner role.

    Thanks

    T

    well, like i mentioned, the specific event for the restore is AUDIT_BACKUP_RESTORE_EVENT (MSDN linky)

    That event can only be handled in an event notification, you get an error like this if you modified my example to include that event:

    Msg 1082, Level 15, State 1, Procedure SERVER_TR_ADD_ClientAdminX_TO_NEW_DBS, Line 27

    "AUDIT_BACKUP_RESTORE_EVENT" does not support synchronous trigger registration.

    My reference for Event Notificatioons is this article:

    http://www.sqlservercentral.com/articles/Event+Notifications/68831/

    i built a nice skeleton for myself based on that article, but never carried it out farther than that.

    basically, you create a procedure that does the work you want for the specific event(s), and the event notification calls the procedure.

    it's a little deeper SQL dive into some of the kewlest SQL functionalities, which is why i mentioned a schedule job doing the work might be easier for you to do

    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!

  • September 28, 2012 at 7:25 am

    #1543407

    Thanks Lowell,

    I love playing with kewl stuff in SQL server, thankfully I've got a couple of instances on VM's I can play around with in case I mess up 😀

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy