SQL Server side trigger

Question

SQL Server side trigger

TJDBA

Default port

Points: 1490
More actions
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

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

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

OTF SSCertifiable Points: 6054 More actions · Answer 1

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Lowell SSC Guru Points: 323504 More actions · Answer 3

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!

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 4

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

TJDBA Default port Points: 1490 More actions · Answer 5

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

Lowell SSC Guru Points: 323504 More actions · Answer 6

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!

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 7

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