March 18, 2019 at 10:19 am
Once the SQL Server gets built by someone, later i would like to get notified about new database creations(this would be server level trigger right?). Automate creating accounts for sql server, is that possible?
March 18, 2019 at 11:58 am
could be trigger, could be sql agent job, could be sql agent alert, could be from another instance monitoring the environment, question is what suits in your environment, only you can decide.
March 18, 2019 at 12:35 pm
Admingod - Monday, March 18, 2019 10:19 AMOnce the SQL Server gets built by someone, later i would like to get notified about new database creations(this would be server level trigger right?). Automate creating accounts for sql server, is that possible?
DDL trigger would be a good option. You can create the trigger to email you when a database is created. The documentation has an example and there are quite a few others on the internet. It's just something like the following: CREATE TRIGGER trCreateDatabase
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @DBName nvarchar(128)
DECLARE @Results nvarchar(2000)
DECLARE @Subject nvarchar(200)
SET @Results = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'))
SET @DBName = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(255)'))
SET @Subject = @DBName + ' database created on ' + @@SERVERNAME + ' by ' + SUSER_NAME()
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'YourProfileName',
@recipients = 'YourEmail.whatever.com',
@body = @results,
@subject = @Subject,
@exclude_query_output = 1
GO
In terms of automating service account creation, the problem would be with setting it up using SQL Server Configuration Manager. There are plenty of posts that are from people using something other than configuration manager for the service account and then having the permissions screwed up. It's not a simple as just adding permissions for the service account as permissions are granted to a group, the service account and the virtual account. And what's used where is a bit different on different versions of SQL Server.
Sue
March 18, 2019 at 1:36 pm
The trigger needs to be deployed on all servers whenever the new SQL Server gets created right?
March 18, 2019 at 1:40 pm
Admingod - Monday, March 18, 2019 1:36 PMThe trigger needs to be deployed on all servers whenever the new SQL Server gets created right?
Yes - it's a trigger for the instance. So adding an instance to an existing server would require the trigger on that new instance as well.
Sue
March 18, 2019 at 2:03 pm
keep in mind you need to setup mail profile as well for this thing to work..
March 20, 2019 at 12:27 pm
Thanks! So when the new SQL Server gets built i would like to do automating service account. How is this possible?
March 20, 2019 at 2:12 pm
please explain 'automating service account'
March 20, 2019 at 2:17 pm
Sorry i wasn't clear. I meant to say every time a new server gets built or database is added to the instance. I add the account's manually instead i would like to automate the process.
March 20, 2019 at 2:44 pm
Admingod - Wednesday, March 20, 2019 2:17 PMSorry i wasn't clear. I meant to say every time a new server gets built or database is added to the instance. I add the account's manually instead i would like to automate the process.
One option is to look at using group managed service accounts and as a standard, have the server built and SQL installed with that account.
You don't have to do anything if a database is added...but you probably already know that once you read this line. 🙂
To change service accounts, you really have to use SQL Server Configuration Manager. To get close to automating that, you'd probably have to write some code with SMO or use Powershell to access Configuration Manager. If you don't go through that, the permissions will get screwed up. There is a Powershell example of doing this in this article - go to the Changing Service Accounts section:
Use PowerShell to Change SQL Server Service Accounts
Getting a service account and password would be something you need to work out with whoever manages Active Directory. And figure out a way to transmit that information safely, not in clear text.
Sue
March 20, 2019 at 2:54 pm
Sorry not sure why i said service accounts. I mean to say Logins and user permissions.
March 21, 2019 at 9:49 am
you can create a script to create your logins and permission and save them at network location, Just run the script after the install that is the automation level you can achieve, cant think of anything else.. unless you have a way to slipstream that script in ISO/install media or you use universal installer to create your own installer but gud luck with that.
May 14, 2019 at 4:34 pm
The above trigger notifies when there is a new database get's created. However, when the database is restored it won't trigger the alert. Is there a way to add the option in this trigger?
May 14, 2019 at 4:44 pm
No, it's not a DDL operation so a DDL trigger won't fire. You have to pick from several not too clear cut options if you want to get notified about any restores. Extended events, polling sys.databases, polling/checking the error log, polling/checking the default trace, etc. There is a blog post on Brent Ozar's site about this very issue - it addresses the options:
Why Is Tracking Restores So Hard?
Sue
May 15, 2019 at 9:34 am
You have to setup a new mail profile in order to get the work done.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply