September 27, 2012 at 7:11 am
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
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
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
September 27, 2012 at 9:50 am
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
September 28, 2012 at 12:13 am
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
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
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
September 28, 2012 at 7:25 am
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
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