Thursday, November 15, 2012

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!!

Tuesday, November 6, 2012

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.