T-Sql & Active Directory Roles, Identification of original login ofimpersonated ID's and Suspended processing

[Tim]

Folks,

I have 3 loosely linked problems which I am would appreciate feedback
on.

1). T-Sql and Active directory roles.
We want to be able to control access to data within a table based on a
role within Active directory.
For example, Region1 has 4 sites, Region 2 has 3 sites and so forth.
All the sites are held in a single database table.
The staff in Region1 must be limited to only being able to retrieve
data for their 4 sites.
We would like to set up an active directory role for each region and
use this to control things.
Is there any way in which T-Sql can retrieve data about the Active
Directory Roles?



2). Drilling back to the original ID when an impersonating ID is used
at database level.
We have legacy systems that allow for an individual login but once the
data request is passed to the database the access is via a generic ID.
This is causing a problem with tracking the authors of inappropriate
changes to the data.
Is there anyway in which T-Sql can drill back to the original ID?



3). Suspended processes.
We are running 2005, to be precise:-

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.0 0
Microsoft Data Access Components (MDAC) 2000.085.1117.0 0
(xpsp_sp2_rtm.0 40803-2158)
Microsoft MSXML 2.6 3.0 4.0
5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

We are finding that the larger SQL statements, in terms of the number
of rows affected, are being 'Suspended' and just hanging for hours.
The term Suspended is taken from the Management>Acti vity
Monitor>Status Column.
We believe that the 'Suspended' is occuring because the process is
waiting on memory but we're not certain.
Does anyone know the cause and / or cure for this 'undocumented
feature'?

TIA, Tim

[Erland Sommarskog]

Re: T-Sql & Active Directory Roles, Identification of original login of impersonated ID's and Suspended processing

Tim (tim_rogers01@h otmail.com) writes:

1). T-Sql and Active directory roles.
We want to be able to control access to data within a table based on a
role within Active directory.
For example, Region1 has 4 sites, Region 2 has 3 sites and so forth.
All the sites are held in a single database table.
The staff in Region1 must be limited to only being able to retrieve
data for their 4 sites.
We would like to set up an active directory role for each region and
use this to control things.
Is there any way in which T-Sql can retrieve data about the Active
Directory Roles?

You can grant DB access to a Windows group, and I think you also
can grant permissions in SQL Server to Windows groups. But you seem
to be looking at some row-level security scheme. In that case, have a
look at the is_member() function.

2). Drilling back to the original ID when an impersonating ID is used
at database level.
We have legacy systems that allow for an individual login but once the
data request is passed to the database the access is via a generic ID.
This is causing a problem with tracking the authors of inappropriate
changes to the data.
Is there anyway in which T-Sql can drill back to the original ID?

It would have helped if you had told how impersonation is done, as
there are several options. But I can think of two ways:

1) Have the generic ID to perform an EXECUTE AS on behalf of the
real user. Note that in SQL 2005 you can add login-less users to
a database.

2) Issue SET CONTEXT_INFO before the generic ID starts working. Then
use the context_info function to retrieve that data in your T-SQL
code.

We are finding that the larger SQL statements, in terms of the number
of rows affected, are being 'Suspended' and just hanging for hours.
The term Suspended is taken from the Management>Acti vity
Monitor>Status Column.
We believe that the 'Suspended' is occuring because the process is
waiting on memory but we're not certain.
Does anyone know the cause and / or cure for this 'undocumented
feature'?

Without knowing more, I would guess on cached connections for linked
servers. I don't think they are waiting for memory.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx