September 24, 2007 at 12:18 pm
We use the following code to determine the Domain Name of the SQL Server.
This is needed to dynamicaly assign permissions to certain AD Groups.
What I'd like to do is dump this code into a UDF, rather than having to embed the code into any SP that might need to apply Permissions in some way.
Unfortunately, you apparantly can't use Temp tables in functions. Nor table variables.
So, anyone have any brainstorms on how to easily retrieve the Domain Name?
DECLARE @SQLString Nvarchar(4000)
DECLARE @DomainName varchar(100)
DECLARE @CharValue int
-- @DomainName
IF OBJECT_ID('tempdb.dbo.#tblDomainName') IS NOT NULL
DROP TABLE #tblDomainName
CREATE TABLE #tblDomainName(name varchar(100), config_value varchar(100))
INSERT #tblDomainName EXECUTE master.dbo.xp_loginconfig 'default domain'
SELECT @CharValue =ASCII(right(config_value,1)) from #tblDomainName
IF @CharValue NOT BETWEEN 48 and 57 -- Numbers
AND @CharValue NOT BETWEEN 65 and 90 -- Upper Case Letters
AND @CharValue NOT BETWEEN 97 and 122 -- Lower Case Letters
BEGIN
SELECT @DomainName = LEFT(UPPER(config_value), LEN(config_value) - 1) FROM #tblDomainName
END
ELSE
SELECT @DomainName = LEFT(UPPER(config_value), LEN(config_value)) FROM #tblDomainName
IF OBJECT_ID('tempdb.dbo.#tblDomainName') IS NOT NULL
DROP TABLE #tblDomainName
September 24, 2007 at 12:30 pm
Any reason not to make it a stored proc of its own?
September 24, 2007 at 3:22 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply