Getting Domain Name in Transact SQL 1

[webuser]

I need to get the name of the Domain that my SQL Server is in using Transact SQL. Is there a function that does this? Like Select GetDomainNameOfMySQLServer()

Thanks in advance!

 

[ptheriault]

I believe you are going to have to read that from the registry.


For example here an extended procedure which will return the account information that your instance of SQL Server is using.
It uses xp_regread. If you know the regkey where the domain name is stored then you can select it. I'm not sure which key in the registry will store that information.

USE master
GO

DROP PROC sp_get_sqlserver_service_account
GO

CREATE PROC sp_get_sqlserver_service_account
AS

-- =============================================
-- Variables
-- =============================================
DECLARE @machinename nvarchar(128),
      @instancename nvarchar(128),
      @profile nvarchar(128),
      @regkey nvarchar(256),
      @regkey2 nvarchar(256),
      @accnt nvarchar(128),
      @accnt2 nvarchar(128)

IF NOT ((charindex(N'7.00', @@version, 0) > 0) OR (charindex(N'8.00', @@version, 0) >
0))
BEGIN
   RAISERROR(N'sp_get_sqlserver_service_account only support SQL Server 7.0 and 2000', 16, 1) 
WITH log, nowait
END

IF (charindex(N'7.00', @@version, 0) > 0)
BEGIN
   SELECT @instancename = NULL
END

IF (charindex(N'8.00', @@version, 0) > 0)
BEGIN
   SELECT   @machinename  = CONVERT(nvarchar(128), serverproperty('MachineName')),
            @instancename = CONVERT(nvarchar(128), serverproperty('InstanceName'))
END

-- =============================================
-- Get account name running SQL Server
-- =============================================
IF (@instancename IS NULL)
BEGIN
    SELECT @regkey = N'SYSTEM\CurrentControlSet\Services\MSSQLServer',
           @regkey2 = N'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
END
ELSE
BEGIN
    SELECT @regkey = N'SYSTEM\CurrentControlSet\Services\MSSQL$' + @instancename,
           @regkey2 = N'SYSTEM\CurrentControlSet\Services\SQLAgent$' + @instancename
END

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                           @regkey,
                           N'ObjectName',
                           @accnt OUTPUT,
                           N'no_output'

EXEC master.dbo.xp_regread N'HKEY_LOCAL_MACHINE',
                           @regkey2,
                           N'ObjectName',
                           @accnt2 OUTPUT,
                           N'no_output'

PRINT 'SQL Server is running under account ' + @accnt

PRINT 'SQL Server Agent is running under account ' + @accnt2
RETURN
GO

EXEC sp_get_sqlserver_service_account

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

I found the regey you will need.

exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon', 'CachePrimaryDomain'
GO

- Paul
- Database performance looks fine, it must be the Network!

 

[webuser]

That's great. Thanks!

But this returns 2 fields - one for value, one for data. What is the best way to set the value column to a variable?

I tried a select from... but it didn't work...

 

[SQLDenis]

like this

declare @dm nvarchar(500)
exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon', N'CachePrimaryDomain',@dm out

select @dm as Domainname

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[webuser]

Perfect!

 

[mrdenny]

In SQL 7/2000 you can use the extended proc xp_ntsec_enumdomains.

This has been removed from SQL 2005 however.

It returns the local machine name, the local domain, and any domais that your domain trusts.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com