Databases and other objects in SQL Server have an owner, which is usually the one who created the object. If a user creates a database and next leaves the company, it is possible that the database no longer has an owner. To prevent errors due to missing or invalid owners, this procedure creates a list of all objects and their owners for every owner that is not listed in the table Acceptable Owners (tbl_Acc_Owners). The following fields are in the report:
LinkedServer
DatabaseName
ObjectName
Owner
Type: The object type that is reported on.
How to use it
The procedure usp_Report_Object_Owner will crawl all Linked Servers that are present on the server where your Sequenchel database resides. Information for the object and their is not stored in the Sequenchel database. In stead the only storage is the Acceptable Owners in the table tbl_Acc_Owners. Any owner found that is not in this table will be reported.
Create a scheduled job using the Settings window. This will create a job with all necessary information. If you prefer to do this manually, execute the stored procedure with the following parameters:
DECLARE @RC int
DECLARE @Recipient varchar(250)
DECLARE @ExceptionList varchar(250)
DECLARE @MailStats bit
DECLARE @SqlVersion int
DECLARE @Separator nchar(1)
SET @Recipient = ‘yourname@yourdomain.com’
SET @ExceptionList = ‘ServerNotToProcess’
SET @MailStats = 1
SET @SqlVersion = 0
SET @Separator = ‘,’
EXECUTE @RC = [dbo].[usp_Report_Object_Owner]
@Recipient
,@ExceptionList
,@MailStats
,@SqlVersion
,@Separator
;
We recommend that you schedule this procedure on a daily basis after the Monitor Servers procedure has run. Any servers not found by the Monitor Servers will be skipped, saving time.
Collected Information
All information that is collected is in the report. The information in the table tbl_Acc_Owners is supplied by the DBA and consists of these parts:
- AccOwnerID: the ID of the record in the table.
- LinkedServer: The Linked Server for which this record is.
- DatabaseName: The specific database for which this record is.
- ObjectName: the specific object for which this record is.
- Owner: The acceptable owner that doesn’t need reporting.
- DateStart: The start date for this owner.
- DateStop: The end date for this acceptable owner.
In the fields Linked Server, DatabaseName and ObjectName wildcards are allowed (%)
The field Owner allows wildcards as well. Beware that if you put a wildcard in every field, nothing gets reported.
Using DateStart en DateStop, you can disable reporting on a specific server, database, object or owner for a specified amount of time for instance when a server is in development.