Automating security within triggers
-
Hello,
I have already a trigger that sends us a mail when a new database is created (some of our users are db_creator). Here's the code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create trigger [newdb]
on ALL Server
WITH EXECUTE AS 'sa'
for CREATE_DATABASE
as
PRINT 'Creating Trigger: newDB'
set nocount on
declare @data xml
declare @message varchar(1000)
declare @instance_name varchar(30)
declare @sujet varchar(150)
set @data = EVENTDATA()
--set @instance_name =
select @instance_name = @@ServerName
SET @message = 'New DB: ' + @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)') + ' created. Check if database mirroring is necessary.'
SET @sujet = 'NEW DATABASE CREATED ON INSTANCE ' + @instance_name
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'email@contoso.com',
@body = @message,
@subject = @sujet ;
PRINT 'Trigger Created'
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ENABLE TRIGGER [newdb] ON ALL SERVER
GO
And it works perfectly.
Well, our backup software needs some rights to backup and restore objects in our instance (for example: item restore in sharepoint).
So I need to give db_backupoperator database role to our backup user whenever a new database is created by the cutsomer.
I was thinking of modifying my trigger by adding some code like:
USE ?
CREATE USER [domain\bck_user] FOR LOGIN [domain\bck_user] ;
EXEC sp_addrolemember N'db_backupoperator', N'domain\bck_user' ;
But obviously, the 'USE' doesn't not work into triggers and therefore I always give my bck user the DB role db_backupoperator to the "master" DB 🙁
So how can I automate the "grant db_backupoperator" for my bck user ?
I would really appreciate if someone has an idea to solve my problem 😛
Thanks in advance.
Have a nice day.
Franck.
-
DECLARE @sql NVARCHAR(MAX) = N'EXEC [YourDatabase]..sp_executesql N''CREATE USER [domain\bck_user] FOR LOGIN [domain\bck_user]''';
EXEC sp_executesql @sql;
SET @sql = N'
EXEC [YourDatabase]..sp_executesql N''EXEC sp_addrolemember N''''db_backupoperator'''', N''''domain\bck_user''''''';
EXEC sp_executesql @sql;
-
Hum, well, it works perfectly, many thanks 🙂
Franck.
-
Or just add the required permission(s) to the model db. Every db created after that will automatically have the same permission(s).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
That's even easier ! How didn't I think that at first :doze: ...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply