32,675
hi,
is there an automation to get the SID for AD users to c/al code, like userSid := adAuto.getSid(domain, ...)?
is it possible to get that by using a sql script an calling that script out from c/al?
best regards
*This post is locked for comments
Check also this one out:
Hi Jonathan,
:)) sorry, didn't see that. Here is this function (which we also found somewhere on the internet, I'd say):
Share
Report
All responses (
Answers (
/****** Object: UserDefinedFunction [dbo].[fn_NAV_ConvertSQLSIDToNavSIDString] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_NAV_ConvertSQLSIDToNavSIDString]
(
@BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS
BEGIN
IF LEN(@BinSID)%4<>0 RETURN(NULL)
DECLARE @StringSID VARCHAR(100)
DECLARE @i AS INT
DECLARE @j AS INT
SELECT @StringSID='S-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,1,1))))
SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(INT,CONVERT(VARBINARY,SUBSTRING(@BinSID,3,6))))
SET @j=9
SET @i=LEN(@BinSID)
WHILE @j<@i
BEGIN
DECLARE @val BINARY(4)
SELECT @val=SUBSTRING(@BinSID,@j,4)
SELECT @StringSID=@StringSID+'-'+CONVERT(VARCHAR,CONVERT(BIGINT,CONVERT(VARBINARY,REVERSE(CONVERT(VARBINARY,@val)))))
SET @j=@j+4
END
RETURN(@StringSID)
END
with best regards
Jens
hi,
it seems that dbo.fn_NAV_ConvertSQLSIDToNavSIDString is a custom sql function by you. so this won't help me.
i found some helping links:
www.mssqltips.com/.../identifying-the-tie-between-logins-and-users
social.msdn.microsoft.com/.../retrieve-guid-or-sid-from-active-directory-via-adsi-and-tsql-only
br
Hi Jonathan,
we have this code in our dbo.sp_$ndo$loginproc:
-- determine SID from NAV for later use
SELECT @NAV_LoginSID = dbo.fn_NAV_ConvertSQLSIDToNavSIDString(sp.sid)
FROM master.sys.server_principals sp where sp.sid = SUSER_SID()
You could call this via ADO. Or, you could add this as a calculated column in a table or view (should be read-only on the NAV side).
with best regards
Jens
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,403
Most Valuable Professional
#3
nmaenpaa
101,156
