Access DB Security Problem - Works on localhost

I have RS 2005 running on a 2003 server.
This report access an Access database located on a network fileshare, which
is a different server than where RS is running. Using Windows Integrated
Security, the report runs fine on the RS server (logged in as myself), but
will not run when I try to access the report from my machine (again, logged
in as myself). The error I get on my machine is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'CAS'. (rsErrorOpeningConnection)
The Microsoft Jet database engine cannot open the file [SERVER]. It is
already opened exclusively by another user, or you need permission to view
its data.
Again, I only get this when accessing from my machine. The report works
fine from the RS host server. The access database is located on a different
machine. The user is the same in both instances, so something else is going
on. Please help.Under the credentials for the data source on the server try putting in a
user and password (store the password) and see if that works. I think what
is hitting you is the double hop issue.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"honus" <honus@.discussions.microsoft.com> wrote in message
news:9B43E798-636B-43BF-9867-9209E5E48B3D@.microsoft.com...
>I have RS 2005 running on a 2003 server.
> This report access an Access database located on a network fileshare,
> which
> is a different server than where RS is running. Using Windows Integrated
> Security, the report runs fine on the RS server (logged in as myself), but
> will not run when I try to access the report from my machine (again,
> logged
> in as myself). The error I get on my machine is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'CAS'.
> (rsErrorOpeningConnection)
> The Microsoft Jet database engine cannot open the file [SERVER]. It is
> already opened exclusively by another user, or you need permission to view
> its data.
> Again, I only get this when accessing from my machine. The report works
> fine from the RS host server. The access database is located on a
> different
> machine. The user is the same in both instances, so something else is
> going
> on. Please help.

Access Control and Security?

Are there any books/article/forum posts out there that document the
best practices for SQL Server security?
For example, what account should create the database?
What account should create the schema?
In a given database should there be a login that is solely responsible
for DDL and a seperate account that only does DML and Queries?
What conventions are people using?
I'm kind of clueless about this and trying to figure out where to
start.
-ThxWhat version of SQL Server are you using? Best practices for SQLS 2000
do not necessarily apply to 2005, where there are expanded options not
available in earlier versions. A good starting point is always SQLS
Books Online, supplemented with a good searh engine :)
-mary
On Wed, 22 Aug 2007 05:58:08 -0700, kilik3000@.gmail.com wrote:
>Are there any books/article/forum posts out there that document the
>best practices for SQL Server security?
>For example, what account should create the database?
>What account should create the schema?
>In a given database should there be a login that is solely responsible
>for DDL and a seperate account that only does DML and Queries?
>What conventions are people using?
>I'm kind of clueless about this and trying to figure out where to
>start.
>-Thx|||On Aug 23, 1:45 pm, "Mary Chipman [MSFT]" <mc...@.online.microsoft.com>
wrote:
> What version of SQL Server are you using? Best practices for SQLS 2000
> do not necessarily apply to 2005, where there are expanded options not
> available in earlier versions. A good starting point is always SQLS
> Books Online, supplemented with a good searh engine :)
> -mary
> On Wed, 22 Aug 2007 05:58:08 -0700, kilik3...@.gmail.com wrote:
> >Are there any books/article/forum posts out there that document the
> >best practices for SQL Server security?
> >For example, what account should create the database?
> >What account should create the schema?
> >In a given database should there be a login that is solely responsible
> >for DDL and a seperate account that only does DML and Queries?
> >What conventions are people using?
> >I'm kind of clueless about this and trying to figure out where to
> >start.
> >-Thx
SQL 2005.
-Thx|||That's a tough one. Basic information is in SQLS Books Online
(http://msdn2.microsoft.com/en-us/library/ms161948.aspx) but it
doesn't directly answer your questions, you have to infer the answers.
Also, your security architecture depends on your needs. DDL has gotten
a lot easier with user-schema separation in 2005 once you understand
it. Erland Sommarskog's web site has some good resources - see Giving
Permissions through Stored Procedures
(http://www.sommarskog.se/grantperm.html).
-mary
On Tue, 28 Aug 2007 20:01:27 -0000, kilik3000@.gmail.com wrote:
>On Aug 23, 1:45 pm, "Mary Chipman [MSFT]" <mc...@.online.microsoft.com>
>wrote:
>> What version of SQL Server are you using? Best practices for SQLS 2000
>> do not necessarily apply to 2005, where there are expanded options not
>> available in earlier versions. A good starting point is always SQLS
>> Books Online, supplemented with a good searh engine :)
>> -mary
>> On Wed, 22 Aug 2007 05:58:08 -0700, kilik3...@.gmail.com wrote:
>> >Are there any books/article/forum posts out there that document the
>> >best practices for SQL Server security?
>> >For example, what account should create the database?
>> >What account should create the schema?
>> >In a given database should there be a login that is solely responsible
>> >for DDL and a seperate account that only does DML and Queries?
>> >What conventions are people using?
>> >I'm kind of clueless about this and trying to figure out where to
>> >start.
>> >-Thx
>SQL 2005.
>-Thx

Access connection to SQL Server

I have had requests form users who would like to use MS Access to connect to
a SQL Server. Are there any security or other issues that would be relevant
to this approach?
RodRod,
This can be slow when linking to and working with large SQL tables (i.e.,
many rows).
HTH
Jerry
"Rod Snyder" <rod@.rcsnyder.com> wrote in message
news:efs3voFxFHA.2880@.TK2MSFTNGP12.phx.gbl...
>I have had requests form users who would like to use MS Access to connect
>to a SQL Server. Are there any security or other issues that would be
>relevant to this approach?
> Rod
>|||Hi Rod,
You can use an ODBC link which would require setting up user accounts and
respective permissions/roles to use and manipulate (update/delete/insert/run
)
sql server objects like Databases, tables, stored procedures.
For large tables, it would be more efficient to retrieve data into MS Access
by calling a Sql Server Stored Procedure with parameters using COM ADO and
populate tables in Access with the retrieved data. You would write the
routines in Access in a code module using VBA. But you would still have to
define a user role for connecting to the Sql Server. For example, you don't
want people connecting to Sql Server using an SA account (system
administrator). That would give everyone access to everything, and they
could do anything. Instead, you have to define a user role which would limi
t
access to respective databases like User1, who can read tbl1, tbl2, ... can
run SP1, SP2, ... in database1 but not database2
User2 can read/write to tables and run SPs in database1 but not database2,
and so on.
Rich
"Rod Snyder" wrote:

> I have had requests form users who would like to use MS Access to connect
to
> a SQL Server. Are there any security or other issues that would be relevan
t
> to this approach?
> Rod
>
>sql

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

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'\',@.DomainUs erName)+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 kyochum@.wthq.com
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.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> 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'\',@.DomainUs erName)+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 kyochum@.wthq.com
>
>
|||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.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> 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[vbcol=seagreen]
> this.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
users[vbcol=seagreen]
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and
>
|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> 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[vbcol=seagreen]
> 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.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
We've[vbcol=seagreen]
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()
> and
>
|||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!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,'')[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
> We've
logs[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info
>
|||> 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)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> 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[vbcol=seagreen]
> to hear about it!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,'')
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
table[vbcol=seagreen]
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values
> context_info
>
|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> 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)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N'\',@.DomainUs erName)+1,LEN(@.DomainUserN[vbcol=seagreen]
> table
row.[vbcol=seagreen]
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()
>

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

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 kyochum@.wthq.comi 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.
"Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> 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[vbc
ol=seagreen]
> 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[/vbcol]
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 kyochum@.wthq.com
>
>|||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.
"aaron kempf" <aarkem@.safeco.com> wrote in message
news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> 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.
>
>
> "Kimberley Yochum" <kyochum@.bigzoo.net> wrote in message
> news:%23BOvPMX0EHA.632@.TK2MSFTNGP10.phx.gbl...
> regardless
stamp[vbcol=seagreen]
> for
> practical
> context_info
> Unfortunately,
that[vbcol=seagreen]
> that
> our
> is
> client
AS[vbcol=seagreen]
> connections
access[vbcol=seagreen]
> for
> we've
logged[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
users
in[vbcol=seagreen]
> odds
the[vbcol=seagreen]
"Application[vbcol=seagreen]
> can
alone.[vbcol=seagreen]
> or
and[vbcol=seagreen]
>|||can you use the application name value instead?
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> 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.
> "aaron kempf" <aarkem@.safeco.com> wrote in message
> news:OF9GDhZ0EHA.2228@.TK2MSFTNGP15.phx.gbl...
> other
want[vbcol=seagreen]
> speed
the[vbcol=seagreen]
very[vbcol=seagreen]
> stamp
way[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> that
row[vbcol=seagreen]
only[vbcol=seagreen]
on[vbcol=seagreen]
fnSystemUser[vbcol=seagreen]
> AS
> access
spids[vbcol=seagreen]
another[vbcol=seagreen]
@.NetAddress)[vbcol=seagreen]
> logged
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
We've
> users
> in
> the
> "Application
We[vbcol=seagreen]
_[vbcol=seagreen]
value[vbcol=seagreen]
but[vbcol=seagreen]
> alone.
HostName()[vbcol=seagreen]
> and
>|||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!!
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
> can you use the application name value instead?
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:%23rCee%23Z0EHA.1404@.TK2MSFTNGP11.phx.gbl...
> name
> want
Access2000[vbcol=seagreen]
> the
proxy[vbcol=seagreen]
> very
on[vbcol=seagreen]
> way
> fnSystemUser
SYSTEM_USER),[vbcol=seagreen]
> row
of[vbcol=seagreen]
> only
> on
> fnSystemUser
through[vbcol=seagreen]
CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)[vbcol=seagreen]
> spids
guarantee[vbcol=seagreen]
> another
> @.NetAddress)
Query[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> We've
logs
the[vbcol=seagreen]
when[vbcol=seagreen]
> We
&[vbcol=seagreen]
> _
Info=False;Application[vbcol=seagreen]
> value
> but
> HostName()
context_info[vbcol=seagreen]
>|||> 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)
"Kimberley Yochum" <kyochum@.wthq.com> wrote in message
news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> 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!!
> "david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
> news:%23sSxhz30EHA.1740@.TK2MSFTNGP15.phx.gbl...
current_user,[vbcol=seagreen]
user[vbcol=seagreen]
the[vbcol=seagreen]
might[vbcol=seagreen]
to[vbcol=seagreen]
> Access2000
work[vbcol=seagreen]
> proxy
to[vbcol=seagreen]
> on
a[vbcol=seagreen]
> SYSTEM_USER),
every[vbcol=seagreen]
life[vbcol=seagreen]
> of
now[vbcol=seagreen]
indicates[vbcol=seagreen]
> through
> CAST(CAST(REPLACE(@.ContextInfo,0x0000,''
)
can't[vbcol=seagreen]
> guarantee
SPACE(50)[vbcol=seagreen]
to[vbcol=seagreen]
> Query
are[vbcol=seagreen]
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
table
2[vbcol=seagreen]
> logs
> the
> when
name.[vbcol=seagreen]
Source=ASQLServer"[vbcol=seagreen]
> &
> Info=False;Application
settings,[vbcol=seagreen]
values[vbcol=seagreen]
> context_info
>|||try with current_user() or user_name()
"david epsom dot com dot au" <david@.epsomdotcomdotau> wrote in message
news:OHiFPga1EHA.2568@.TK2MSFTNGP11.phx.gbl...
> 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)
>
> "Kimberley Yochum" <kyochum@.wthq.com> wrote in message
> news:OhSjrRA1EHA.3448@.TK2MSFTNGP09.phx.gbl...
> connection
> it
hostname[vbcol=seagreen]
> those
> Source=ASQLServer"
> love
> current_user,
> user
> the
> might
order[vbcol=seagreen]
> to
> work
method.[vbcol=seagreen]
very,[vbcol=seagreen]
spids,[vbcol=seagreen]
> to
depends[vbcol=seagreen]
need[vbcol=seagreen]
> a
not[vbcol=seagreen]
> every
> life
was[vbcol=seagreen]
> now
> indicates
> can't
other[vbcol=seagreen]
> SPACE(50)
Context_Info[vbcol=seagreen]
> to
> are
>
SUBSTRING(@.DomainUserName,CHARINDEX(N''
,@.DomainUserName)+1,LEN(@.DomainUserN[vbc
ol=seagreen]
> table
row.
seen[vbcol=seagreen]
> 2
user[vbcol=seagreen]
but[vbcol=seagreen]
spid[vbcol=seagreen]
> name.
> Source=ASQLServer"
those[vbcol=seagreen]
> settings,
> values
SYSTEM_USER()[vbcol=seagreen]
>sql

access

oops.. excuse french..
i have a client that i have 2 db on server, but there are a lot of db, and
the security is present!. 1 is for live operation, 1 is for test. The
problem is that i cannot restore db on the test. But i have DBO and backup
operator access. So.. there is a way for me to restore the db and preserve
access for another db ?
thank!Hi,
Restore can be done by an user who got "dbcreator" fixed server role or
"sysadmin" fixed role.
How to assign "DBCREATOR" to a user:-
sp_addsrvrolemember <user>,'dbcreator'
Since you are doing the backup and restore in the same SQL Server machine,
the access previlages will not be a problem.
The access issues will only araise when you restore the database to a new
SQL Server, where your Ligin/User chain will be broken.
This case you can use the system procedure "sp_change_users_login" (refer
books online) to recreate the link.
Thanks
Hari
MCDBA
"Marty" <martin.foucault@.nospam.cgsi.qc.ca> wrote in message
news:#APmf3HPEHA.2740@.TK2MSFTNGP11.phx.gbl...
> oops.. excuse french..
> i have a client that i have 2 db on server, but there are a lot of db, and
> the security is present!. 1 is for live operation, 1 is for test. The
> problem is that i cannot restore db on the test. But i have DBO and
backup
> operator access. So.. there is a way for me to restore the db and
preserve
> access for another db ?
> thank!
>

About the security

Dear all,
How to indetify if there is a trace running against a sql server or not? I
will try to explain better. I've got lots of sql2k remote servers added into
my EMC client. Up to here that's fine. But due to a big complexity of our
organization sometimes oneself have serious doubts about of the "do and
donts" (you could think that's ridiculous or even amazing but that's the
truth)
If I perfectly know that auditing process are running then I don't want do
any SELECT, obviously.
"exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
Profiler' obvioulsy say us that Sql tool for that is running. But the
question is, if were another tool disguissed doing that?
You never will have the total security of that anybody is spying one server.
thanks in advance,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)It may be going off at a bit of a tangent, but if you need to be that
sure that no-ones going to capture your SQL statements, have you
considered encrypting the statement, sending that to an SP that will
decrypt it and then run it using sp_executesql. This way the actual
statement should never be seen as it is executed as part of the outer
stored procedure. Only I can't see why if someone really wanted to get
at your statements they couldn't just read the packets going through,
and never actually interface with the SQL server (though I admit I do
not know how SQL server actually sends its instructions - perhaps they
are already encrypted while being sent).|||fn_trace_getinfo
"Enric" wrote:

> Dear all,
> How to indetify if there is a trace running against a sql server or not? I
> will try to explain better. I've got lots of sql2k remote servers added in
to
> my EMC client. Up to here that's fine. But due to a big complexity of our
> organization sometimes oneself have serious doubts about of the "do and
> donts" (you could think that's ridiculous or even amazing but that's the
> truth)
> If I perfectly know that auditing process are running then I don't want do
> any SELECT, obviously.
> "exec sp_trace_getdata 2, 0" in current activity and along with 'Sql
> Profiler' obvioulsy say us that Sql tool for that is running. But the
> question is, if were another tool disguissed doing that?
> You never will have the total security of that anybody is spying one serve
r.
> thanks in advance,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)

about SQL String (security question)

I have a SQL Query String like below..

string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";

in my program it does work perfectly ... but now I just recalled my teacher have told.. it may cause security problem in that way...

maybe I need to change 【UPDATE Member SET Member_pwd = '" + pwd +】 to【UPDATE Member SET Member_pwd = '" + @.pwd +】

is there any difference between pwd and @.pwd?

thank you very much

I think what your teacher suggested is to use Parameterized Queries. There are a few benefits to it - primarily - security, maintenance/ease of coding. Please google for more info.I can type up a couple of lines but you will learn more from the articles already published.|||

Using this dynamic query building with strings is bad. Users can potentially inject malicious code into your queries. I have included a helpful link for you to review:http://www.4guysfromrolla.com/webtech/092601-1.shtml

Good Luck!

|||

thanks for you all (very much)

do you have a example for C#... shame on me I'm not similiar with VB...

I just trying to fix my original code . can you give me some suggestion?

except I have to change pwd to @.pwd.. is something else I've to add or edit?

thank you very much

================start of original code ============================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

================end of original code ============================

================fixed code ===================================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + @.pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

|||The post is marked as answered. So I am assuming you got your solution?|||

no no no... I'm sorry

should I post a new topic for extra question?

I still have many wonder on this filed...

thank you

|||

Hi,

You will need to add the parameters for all the criterias that is used in the query. Not only the pwd.

A typical sample might look like

SqlCommand cmd = new SqlCommand("Select * from Table WHERE [name]=@.name", this.connection);
cmd.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
cmd.Parameters.Value = "Kevin";
SqlDataReader sdr = cmd.ExecuteReader();

about security of SQL Server

hi;
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...
Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...
|||hi Hari;
i'm from Turkey
Thank you very much...
"Hari" wrote:

> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
>
>

about security of SQL Server

hi;
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
--
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...|||hi Hari;
i'm from Turkey
Thank you very much...:)
"Hari" wrote:
> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> > hi;
> > i have install an sql-server desktop edition on winXP
> > how can change my "sa" password
> > thank you...
>
>

about security of SQL Server

hi;
i have install an sql-server desktop edition on winXP
how can change my "sa" password
thank you...Hi,
From command prompt execute OSQL to login to sql server MSDE:-
OSQL -Usa -Ppassword -S Server_name (enter Key)
1>sp_password null,'newpassword','sa'
2>go
THis will change the password of sa.
Thanks
Hari
MCDBA
"Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
> hi;
> i have install an sql-server desktop edition on winXP
> how can change my "sa" password
> thank you...|||hi Hari;
i'm from Turkey
Thank you very much...
"Hari" wrote:

> Hi,
> From command prompt execute OSQL to login to sql server MSDE:-
> OSQL -Usa -Ppassword -S Server_name (enter Key)
> 1>sp_password null,'newpassword','sa'
> 2>go
> THis will change the password of sa.
> --
> Thanks
> Hari
> MCDBA
> "Ferhat" <Ferhat@.discussions.microsoft.com> wrote in message
> news:9C3D68D6-B2D5-499E-91B4-3CE70E4D44E6@.microsoft.com...
>
>

About Security between views and tables

Hi,

I've got a table T1 and a view V1 based on T1's rows (but not all rows).

I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.

then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.

Is there a way of doing this ? (sql 2000).

You can't update a view. A view isn't a table. It's just a select statement.|||

Yes i can. I did it many times, as the select is not too complex, you can do it.

For insance, if you got a table T1 with fields (ID, f1,f2,f3,f4) and a view V1 as select id, f1,f2 from t1, you 'll be allowed to make updates.

this feature works fine with many views i wrote.

|||Well, I'll be...

Have you read through this document to see if you have met the criteria for updateable views? http://msdn2.microsoft.com/en-gb/library/ms187956.aspx|||

thanks for the link. yes my view is updateable, and when there's no grant restrictions, it works fine.

My problem is the following : I'm working on an ms access migration to sql 2000.
Then, i use my view as an attached table in access, an users can modify some values.
But i don't wan basic users have a direct access to the table, only to the view.
That's why i tried to restrict select and update on the table, whereas select and updates are allowed to the view.

But i'm afraid i won't be able to do this...

|||If you have the base table restricted, I'm wondering if the view can supercede that security. I'm thinking not.|||

i'm afraid you're right. so i'll have to manage my own security... :-(

about security

A lot of time the norton antivirus block an atemp to hack on my computer from an ip called himself nemo

the problem is that after the installation of MSSEE with advanced service I found that the mssee want to connect to a server called nemo and that the admin name is nemo

so whatthat suppose to mean?

ahere is a part of the log file:

Opening 'MachineConfigScope' for [NEMO]
Trying to find Product Code from command line or passed transform
If possible, determine install id and type
Trying to find Instance Name from command line.
No Instance Name provided on the command line
If possible, determine action
Machine = NEMO, Article = WMIServiceWin32OSWorking, Result = 0 (0x0)
Machine = NEMO, Article = WMIServiceWin32CompSystemWorking, Result = 0 (0x0)
Machine = NEMO, Article = WMIServiceWin32ProcessorWorking, Result = 0 (0x0)

Moving to SQL Security forum.|||

I am so sorry I discovered that the system name of my computer is nemo

so the mssee is secure

I am aufully sorry

youssef

about intergrated security

hi all

i am setting the sqlconnection string right this:

server="myserver";Intergrated security = "sspi"; database="mydb";

do i need to add ASPNET account to the sql server in order to get it works?

any dangerous do i need to face when i set the connection string right that?

thanks

Using Integrated Security, the Windows ASPNET user must be a user in the database.