Access 2003 adp/proxy security - A substitute for SYSTEM_USER()

[Kimberley Yochum]

We are moving an Access2000 adp application to Access 2003. Access2000
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.

Problem

We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.

Details:

a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution

We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.

Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:


CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN

DECLARE @ContextInfo varbinary(128)
DECLARE @DomainUserName nchar(128)

-- First attempt to get the username stuffed into
-- context_info of this connection

SELECT @ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @@SPID

-- Convert it to nvarchar
SET @DomainUserName = CAST(CAST(REPLACE(@ContextInfo,0x0000,'') AS
varbinary(128)) AS nvarchar(50))

-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid

IF LEN(@DomainUserName) < 1
BEGIN
DECLARE @NetAddress nchar(12)

SELECT TOP 1 @NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @@SPID

SELECT TOP 1 @ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@NetAddress) < 1 OR net_address = @NetAddress)
ORDER BY Context_info

SET @DomainUserName =
CAST(CAST(REPLACE(@ContextInfo,0x0000,'') AS varbinary(128)) AS
nvarchar(50))

END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@DomainUserName) < 1
SET @DomainUserName = SYSTEM_USER

RETURN
SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN
ame)-(CHARINDEX(N'\',@DomainUserName)))
END

GO

Solving the performance problem

Here are some workaround we've tried:

a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"

Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing


But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.

If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()

a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!

Kimberley Yochum (e-mail address removed)

 

[aaron kempf]

i dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right??

also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.

We are moving an Access2000 adp application to Access 2003. Access2000
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.

Problem

We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.

Details:

a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution

We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.

Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:


CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN

DECLARE @ContextInfo varbinary(128)
DECLARE @DomainUserName nchar(128)

-- First attempt to get the username stuffed into
-- context_info of this connection

SELECT @ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @@SPID

-- Convert it to nvarchar
SET @DomainUserName = CAST(CAST(REPLACE(@ContextInfo,0x0000,'') AS
varbinary(128)) AS nvarchar(50))

-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid

IF LEN(@DomainUserName) < 1
BEGIN
DECLARE @NetAddress nchar(12)

SELECT TOP 1 @NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @@SPID

SELECT TOP 1 @ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@NetAddress) < 1 OR net_address = @NetAddress)
ORDER BY Context_info

SET @DomainUserName =
CAST(CAST(REPLACE(@ContextInfo,0x0000,'') AS varbinary(128)) AS
nvarchar(50))

END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@DomainUserName) < 1
SET @DomainUserName = SYSTEM_USER

RETURN
SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN
ame)-(CHARINDEX(N'\',@DomainUserName)))
END

GO

Solving the performance problem

Here are some workaround we've tried:

a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"

Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing


But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.

If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()

a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!

Kimberley Yochum (e-mail address removed)

 

[Kimberley Yochum]

Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.

Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...

Thank you in advance.

i dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right??

also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.

We are moving an Access2000 adp application to Access 2003. Access2000
version uses approle for security but we found Approle does not work the
same in Access2003 so we are switching to a proxy security method.

Problem

We need a way for SQL to know the nt username that initiated the proxy
connection. We need SQL to be able to retrieve that username very, very
quickly (basically without a table lookup) for each user's spids, regardless
of how many connections Access decides to make for the adp.

Details:

a.. All the insert and update triggers depended on SYSTEM_USER to stamp
the user who wrote the data on the row
b.. Many stored procedures and views use a UDF that also depends on
SYSTEM_USER
c.. Since we're reconnecting each user as a proxy user, we need a way for
SQL to determine the nt user of the application.
d.. Note: Modifying the client to pass the user in is simply not practical
due to the huge amount of code change.
Our solution

We are using SET CONTEXT_INFO to stuff the user's name into the context_info
of sysprocesses when the app first logs in. We have a UDF - fnSystemUser
that queries sysprocesses to return what SYSTEM_USER used to. Unfortunately,
for the triggers or any UDFs (which previously only hit SYSTEM_USER), that
meant a nose dive in performance since the UDF is executing on every row
instead of evaluating once as if it were deterministic for the life of that
query. Although SYSTEM_USER is nondeterministic, it appears it was only
evaluated once for the queries instead of the performance we see now on our
UDF returning the system user name from context_info, that indicates
executing on every row.

Since Access adps open multiple connections dynamically our fnSystemUser is
coded to find the Context_info of the spid that was first set through client
code. See the code below:


CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN

DECLARE @ContextInfo varbinary(128)
DECLARE @DomainUserName nchar(128)

-- First attempt to get the username stuffed into
-- context_info of this connection

SELECT @ContextInfo = context_info
FROM master..sysprocesses
WHERE spid = @@SPID

-- Convert it to nvarchar
SET @DomainUserName = CAST(CAST(REPLACE(@ContextInfo,0x0000,'') AS
varbinary(128)) AS nvarchar(50))

-- If the context info is blank then we're on one of the connections
-- that Access dynamically created but the client code can't access
to stuff
-- something into the context_info. Interrogate all other spids for
this
-- user and this client process (using net_address to guarantee we've
got the
-- right user), to pull username from the context info of another
connection/spid

IF LEN(@DomainUserName) < 1
BEGIN
DECLARE @NetAddress nchar(12)

SELECT TOP 1 @NetAddress = net_address
FROM master..sysprocesses p
WHERE SPID = @@SPID

SELECT TOP 1 @ContextInfo = context_info
FROM master..sysprocesses p
INNER JOIN master..sysdatabases d
on p.dbid = d.dbid
WHERE hostprocess = HOST_ID()
AND d.Name = DB_NAME()
AND NOT CAST(context_info as nchar(50))= SPACE(50)
AND (LEN(@NetAddress) < 1 OR net_address = @NetAddress)
ORDER BY Context_info

SET @DomainUserName =
CAST(CAST(REPLACE(@ContextInfo,0x0000,'') AS varbinary(128)) AS
nvarchar(50))

END
/* Fail safe, if we couldn't find a non-empty Context_Info to
discover the
logged in user then could be the user is attached with Query
Analyzer or
some other method and we'll just use the user as they are logged
into SQL */
IF LEN(@DomainUserName) < 1
SET @DomainUserName = SYSTEM_USER

RETURN

SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN

ame)-(CHARINDEX(N'\',@DomainUserName)))
END

GO

Solving the performance problem

Here are some workaround we've tried:

a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"

Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing


But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.

If we could get this to work then it would be ideal. Querying HostName() or
even Appname() although obscure would be as fast as SYSTEM_USER()

a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!

Kimberley Yochum (e-mail address removed)

 

[david epsom dot com dot au]

can you use the application name value instead?

Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.

Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...

Thank you in advance.

i dont want to sound dumb, but you've tried SUSER_SNAME() and all the other
ones, right??

also, if you're scanning against a large list of Text data, you might want
to consider using HASH INDEXES (aka the checksum function) in order to speed
this.




way
for on
our fnSystemUser
is spids
for

SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN

ame)-(CHARINDEX(N'\',@DomainUserName)))
END

GO

Solving the performance problem

Here are some workaround we've tried:

a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user logs in
that HostID() and HostName() will uniquely identify each user. but the odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks) when the
user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name.

We
can

get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer" & _
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"

Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing


But when we try this in Access2003, Access seems to stomp on those value
when it connects. We've played with the Connection dialog settings, but
can't seem to convince Access to leave our connection string values alone.

If we could get this to work then it would be ideal. Querying

HostName()
or

even Appname() although obscure would be as fast as SYSTEM_USER()

a.. Could we somehow create a function that would wrap context_info and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!

Kimberley Yochum (e-mail address removed)

 

[Kimberley Yochum]

Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.

Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"


Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!

can you use the application name value instead?

Thank you Aaron, but all the user functions (System_user, current_user,
session_user, user, user_name, suser_sname, etc.) return the Proxy user name
once a SQL user login has been performed.

Still hoping for some understanding about what Access is doing to my
connection string values or suggestions on what we can do in SQL...

Thank you in advance.

SYSTEM_USER),
that CAST(CAST(REPLACE(@ContextInfo,0x0000,'')
AS

SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN

ame)-(CHARINDEX(N'\',@DomainUserName)))
END

GO

Solving the performance problem

Here are some workaround we've tried:

a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2 users
have the same HostName. So there is no guarantee that when a user

logs
in

that HostID() and HostName() will uniquely identify each user. but the
odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks)

when
the

user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either "Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name. We
can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"

&

_ context_info
and

 

[david epsom dot com dot au]

string are being overwritten by Access.

rats. :~(

I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.

(david)

Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.

Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"


Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!

can you use the application name value instead?

user
name might
want work
the a
way every
row

life

of now
on

SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN

ame)-(CHARINDEX(N'\',@DomainUserName)))
END

GO

Solving the performance problem

Here are some workaround we've tried:

a.. Querying sysprocesses seems slow so we instead created a table
SyUserLogin and use HostID() and HostName() to find the right row. We've
seen SQL change HostName (or Workstation ID) to "Pool08" and seen 2
users
have the same HostName. So there is no guarantee that when a user logs
in
that HostID() and HostName() will uniquely identify each user. but the
odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks) when
the
user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either
"Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user

name.
We

can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data

Source=ASQLServer"

& settings,
but

 

[vichet]

try with current_user() or user_name()

string are being overwritten by Access.

rats. :~(

I'm an Access person, and I don't know anything else you could
try on the Access side. I guess you will be looking for a way
to cache the value, so that fnSystemUser doesn't need to do
SELECT query on every call, but I don't know what the options
are in SQL Server.

(david)

Yes I would love to but all my attempts to set an App name in the connection
string are being overwritten by Access.

Here's the connection string I tried. When I tried this in a VB script, it
worked fine and sysprocess showed the appname "MyTestApp" and the hostname
"NTUserName". But when I programmatically set the Access
CurrentProject.Connection by opening with the same connection string, those
value get tossed.

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data Source=ASQLServer"
& _
";Initial Catalog=MyDatabase" & _
";Persist Security
Info=False;Application Name=MyTestApp;Workstation ID=NTUserName;"


Any suggestions? Have you had any success in setting this before? I'd love
to hear about it!!

order

to depends
on

need

a life

Context_Info

to

SUBSTRING(@DomainUserName,CHARINDEX(N'\',@DomainUserName)+1,LEN(@DomainUserN seen

2

users
have the same HostName. So there is no guarantee that when a

user
logs

in
that HostID() and HostName() will uniquely identify each user.

but
the

odds
are in our favor.

Basically here's what runs (stripped of the uniqueness checks) when
the
user first logs in to SQL:

INSERT INTO [dbo].[SyUserLogin]
([UserName], [HostName], [HostID], [NetAddress])
SELECT @SystemUserName, -- username that we pass in
Host_Name(),
Host_id(),
@NetAddress -- from the sysprocesses row for this spid

Here's the new fnSystemUser:

CREATE FUNCTION dbo.fnSystemUser()
RETURNS nvarchar(50)
AS
BEGIN
RETURN
(SELECT UpdateUserName
FROM SyUserLogin
WHERE HostName = Host_NAME()
AND HostID = HOST_ID()
)
END


a.. We tried to change the connection string to use either
"Application
Name" or "Workstation ID" as a cubby hole to stuff the nt user name.
We
can
get this to work in a VB Script:
dim objConnection
dim strAccessConnect

strAccessConnect = "Provider=SQLOLEDB.1" & _
";Net=dbnmpntw;Data

Source=ASQLServer"

&
_
";Initial Catalog=MyDatabase" & _
";Persist Security Info=False;Application
Name=MyTestApp;Workstation ID=NTUserName;"

Set objConnection = createobject("ADODB.Connection")
objConnection.Open strAccessConnect, "OurProxy", "ProxyPassword"
Msgbox objConnection.ConnectionString
objconnection.close
set objconnection = nothing


But when we try this in Access2003, Access seems to stomp on those
value
when it connects. We've played with the Connection dialog settings,
but
can't seem to convince Access to leave our connection string values
alone.

If we could get this to work then it would be ideal. Querying
HostName()
or
even Appname() although obscure would be as fast as SYSTEM_USER()

a.. Could we somehow create a function that would wrap context_info
and
match the speed of SYSTEM_USER?
Thank you in advance for reading through this and commenting!

Kimberley Yochum (e-mail address removed)