RE: I'm so confused

From: Bill Hollinshead [MS] (billhol@online.microsoft.com)
Date: 10/24/02 

From: billhol@online.microsoft.com ("Bill Hollinshead [MS]")
Date: Thu, 24 Oct 2002 18:11:58 GMT

Hi John,

Running a SELECT @@trancount just before calling sp_grantdbaccess will
confirm whether the error may be appropriate. However, seeing an
@@trancount >0 (when not expected) could be due to the MDAC bug in
http://support.microsoft.com/support/kb/articles/q281/6/33.asp. To
determine that expectation, consider the connection's IMPLICIT_TRANSACTIONS
setting (or SELECT @@OPTIONS), or, you can determine that expectation (if
you know that SET IMPLICIT_TRANSACTIONS was never run for that
session/connection) via Query Analyzer's menu, Tools|Options|Connection
Properties). If you suspect that bug, then try opening a new connection and
testing your sproc (or apply the latest MDAC service pack to the client
where your tests are being made).

Back to sp_grantdbaccess.
Were SQL Server to allow a developer the ability to execute
sp_grantdbaccess within a transaction, then SQL Server would be allowing a
user/developer to put the system catalog at the developer's/user's mercy.
For example, the developer/user could create a dialog box that opens a
transaction (when the dialog box opened), yet the dialog box's code could
be written so that the transaction is not committed until that dialog is
closed. Thus, a user/developer could open that dialog, take a vacation,
come back, realize that he needed to click OK, and thus lock a critical
part of SQL Server's security system for an indefinite period of time <g>.
More commonly, a developer could forget to close a transaction (and/or
check the trancount) within their scripts and thus do similar damage.

If not already considered, your SQL Server security administration overhead
can be reduced by first creating an NT group just for SQL Server access,
putting your NT accounts within that group, and then just granting that
group dbaccess.

Finally, if you need to grant access of a lot of accounts (to different
databases), then perhaps it would be better to write client-side code
(using OLE DB or ODBC) that would instead perform that work (of the T-SQL)
upon a client (one database at a time). For example, consider a situation
where the clients all exist in one domain, while SQL Server exists in
another domain, and where SQL Server's domain trusts the clients' domain
(but not the other way around). In such a situation it would perhaps be
more straight-forward to run the sp_grantdbaccess code in the client's
domain (to gather the client domain's NT account names) and thus (for this
situation) it is likely better to run the code at a client.

Thanks,

Bill Hollinshead
Microsoft, SQL Server

This posting is provided "AS IS" with no warranties, and confers no
rights. Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Relevant Pages

  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... I think it will ameliorate the situation if you clean up the client as ... And to come back to my problem: I think with help of the SQL Server admins ... closed connections - but all of these errors are in the version which used ... Every new client opens again 30 connections if I open 30 tables ...
    (microsoft.public.sqlserver.connect)
  • Re: Best way to populate webpages from multiple tables
    ... tables within hash tables, custom classes etc. ... > Set a session expiring cookie at the client that stores the Customer ID. ... > send it to the client, instead store it in your session mgmt sql server - ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Best way to populate webpages from multiple tables
    ... tables within hash tables, custom classes etc. ... > Set a session expiring cookie at the client that stores the Customer ID. ... > send it to the client, instead store it in your session mgmt sql server - ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access 2007->SQL Server2005 "connection was forcibly closed",G
    ... an Access database in former times this was a lot of work which took me ... closed connections - but all of these errors are in the version which used ... the SQL Server 2000 and everything worked ... client for programming easy, fast and good applications. ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL and Bandwidth use
    ... In addition to the other responses, take a look at the memory and CPU usage ... in Great Plains Client software. ... > near 100Mb/sec bandwidth). ... Client PC is on same segment as SQL server (not passing through ...
    (microsoft.public.sqlserver.server)