sp_ActiveDirectory_Obj (Transact-SQL)

Controls the registration of a Microsoft SQL Server database in the Microsoft Windows Active Directory.

Topic link iconTransact-SQL Syntax Conventions

Syntax

sp_ActiveDirectory_Obj [ @Action=N'action'
     [ , [@ObjType= ] N'database' ]
     , [ @ObjName= ] N'database_name'

Arguments

  • [ @Action = ] N'action'
    Specifies whether the Active Directory object registering the SQL Server database is to be created, updated, or deleted. action is nvarchar(20) with a default of N'create'.

    Value

    Description

    create

    Registers the SQL Server database in the Active Directory by creating an MS-SQL-SQLDatabase object in the directory. The MS-SQL-SQLDatabase object records the attributes of the database at the time the action is performed. If you specify this value and the database is already registered, the object is updated.

    update

    Refreshes the attributes registered for the database in the Active Directory by updating the attributes recorded in the MS-SQL-SQLDatabase object in the Active Directory.

    delete

    Removes the Active Directory registration for the database by deleting the MS-SQL_SQLDatabase object from the Active Directory.

  • [ @ObjType = ] N'database'
    Specifies that sp_ActiveDirectory_Obj perform the requested action on a database object in the Active Directory. N'database' is nvarchar(15), with a default of N'database'. This is the only supported value.

  • [ @ObjName = ] N'database_name'
    Specifies the name of the database for which the registration action is performed. database_name is sysname, and you must specify a value. database_name must specify the name of a database that exists in the instance of SQL Server in which sp_ActiveDirectory_Obj is executed. database_name must conform to the rules for identifiers.

Return Code Values

0 (success) or 1 (failure)

Result Sets

None

Remarks

The current instance of SQL Server must be registered in the Active Directory before you can register any of the databases in the instance. If you remove the registration of the instance from the Active Directory, all of the registrations for databases in that instance are also removed.

Databases are the only entities you can register in the Active Directory by using sp_ActiveDirectory_Obj directly. To control the registration of instances of SQL Server in the Active Directory, use sp_ActiveDirectory_SCP.

Note

Active Directory registration can only be modified by the account that created it. When you change the security account used by the SQL Server service, delete the Active Directory registration, change the service account, and then create the Active Directory registration again.

Permissions

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

Examples

The following example registers in the Active Directory, the AdventureWorks2008R2 database from the current instance of SQL Server.

DECLARE @RetCode INT

EXEC @RetCode = sp_ActiveDirectory_Obj @Action = N'create',
     @ObjType = N'database',
     @ObjName = N'AdventureWorks2008R2';

PRINT 'Return code = ' + CAST(@RetCode AS VARCHAR);