Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Automating security within triggers Expand / Collapse
Author
Message
Posted Tuesday, October 9, 2012 7:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:03 AM
Points: 36, Visits: 289
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.
Post #1370378
Posted Tuesday, October 9, 2012 8:05 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 943, Visits: 2,951
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;





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1370410
Posted Tuesday, October 9, 2012 8:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:03 AM
Points: 36, Visits: 289
Hum, well, it works perfectly, many thanks

Franck.
Post #1370417
Posted Tuesday, October 9, 2012 11:47 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:33 PM
Points: 2,208, Visits: 3,323
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)

Carl Sagan said: "There is no such thing as a dumb question." Sagan obviously never watched a congressional hearing!
Post #1370516
Posted Wednesday, October 10, 2012 2:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:03 AM
Points: 36, Visits: 289
That's even easier ! How didn't I think that at first ...
Post #1370751
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse