How to send notifications automatically in case any new database is created.

Today I faced a new quest that is to raise an alarm incase if any new database is created in the server.

I have scouted in Google as a first step J but didn’t find any script meeting my requirements so wrote a script of my own.

So the best way I thought of achieving this is through server level DDL trigger which gets fired automatically when anyone creates a new database. I used msdb.dbo.sp_send_dbmail inside the trigger to send mail to respective recepients. In the mail being sent I need information like when the database was created, what is the recovery model of it, who is the owner of the database, collation. Based on requirement we can get other information as well.

IF EXISTS (SELECT * FROM sys.server_triggers

    WHERE name = 'Alert_Database_Creation')

DROP TRIGGER Alert_Database_Creation

ON ALL SERVER;

GO

CREATE TRIGGER Alert_Database_Creation

ON ALL SERVER

FOR CREATE_DATABASE

AS

Declare @sub varchar(2000),@srv varchar(2000),@dbname varchar(2000),@bod varchar(2000)
Declare @recoverymodel varchar(2000),@collation varchar(2000),@datecreated varchar(2000),@owner varchar(255)

select @srv= @@SERVERNAME

select @dbname = name,@owner = SUSER_SNAME(owner_sid), @collation = collation_name,@datecreated =
(SELECT CONVERT(VARCHAR(20), create_date, 100))
,@recoverymodel= recovery_model_desc from sys.databases
where (DATEPART(DD,create_date) = DATEPART(DD,GETDATE())) AND (DATEPART(YYYY,create_date) = DATEPART(YYYY,GETDATE()))
AND (DATEPART(MM,create_date) = DATEPART(MM,GETDATE()))
 

    select @sub = 'New Database Has Been Created in server '+ @srv

    Select @bod = 'Database Name    : '+ UPPER(@dbname) + Char(13)+'Database Owner  : '+@owner+ CHAR(13)+'Recovery Model  : '+@recoverymodel

    +CHAR(13)+'Collation                 : '+@collation+CHAR(13)+'Created Date        : '+@datecreated

    EXEC msdb.dbo.sp_send_dbmail

      @profile_name = 'profile name',

      @recipients = 'email@address’,

      @body = @bod,

      @subject =@sub

Thanks!!

Property Owner is not available for Database '[databasename]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

This error sometimes appear while opening the properties of the database in most cases reason would be due to missing of database owner. Reason could be the current owner of the database is no longer valid it would have been removed from the server or would have been disabled or other reasons. So as the owner is not available SQL server will not allow us to see the properties of the database and will throw below error:

 

Property Owner is not available for Database '[databasename]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.  (Microsoft.SqlServer.Smo)

 

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.2500.0+((KJ_PCU_Main).110617-0038+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476

 

Once we change the owner of the database this issue will get fixed automatically. As we can’t access the property of the database through GUI we need to use the below command to change the owner of the database:

 

Sp_changedbowner ‘username’

 

The above command will change the owner of the current database. After changing the owner we will be able to see all the properties of the database.

User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

After successfully installing report services and configuring all options in ‘Reporting Services Configuration Manager’ while trying to access the URL under ‘Reporting Manager URL’ section of configuration manager its asking to provide credentials which is weird as it should directly connect. To avoid this we need to add the respective reporting URL to ‘Trusted Site’ zone in iexplorer Tools - > Internet Options -> Security -> Trusted Sites -> Sites.

Now after adding the reporting URL to trusted sites zone it’s not asking for credentials but after connecting throwing below error:

User '' does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.

This happens when the user does not have the permission to the reporting url, even if you have windows admin privileges, access to reports URL should be given explicitly. This happens mostly during fresh installations, in such case you need to open iexplorer or browser with administrator credentials like shown in screenshot below:

Right click on iexplorer and select the option ‘Run as administrator’, once browser will open, open the report url and in url on right hand top corner you will find option ‘ Site Settings’ shown in screen shot below

 

 

Click on Site Settings and in it click on ‘Security’ tab

 

 

Under security tab click on ‘New Role Assignment’ and add the user to whom you want to give the access to SSRS URL and click on OK. In our case you can add the user with which you logged into machine and give him ‘System Administrator’ and ‘System User’ roles. After giving permission you should not receive this error any more.

Thanks!!