Re: Getting the windows username (of the connection)
- From: Alain Trépanier <AlainTrpanier@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 1 Dec 2008 08:44:01 -0800
Will I be able to obtain the effective user nt username and domain name using
the domain group?
If not, I would then have to use the guest account and modify my ExecUserNt
stored procedure (below is the code) so that it only accept certain domains
or I can forget it and use SYSTEM_USER, that way I keep the domain with the
username (I wille then have to also keep domain/username in my user tables)...
Another quick question. Here at Bell we still use Sql Server 2000, We have
2008 and I'd like to tell "backup from 2000 and restore to 2008 and voila" or
even "backup from 2000 and restore to 2005 and voila" and I may have to set
compatibility level to 2000, but only if necessary. I know we might also have
to add linked servers but I see nothing more. DBA here seem to be afraid to
do the move (2000 to 2005 or 2008), but me as developer I tend to use the
latest weapons.
Thank you for the response you gave me!
"Russell Fields" wrote:
Alain,.
That simplified things for you, but I prefer to avoid using guest. It may
make things more open than you wish, especially if another domain is able to
also access your SQL Server. I prefer to use domain groups.
GRANT CONNECT TO MyDomain\SQLServerPublic
The group MyDomain\SQLServerPublic can contain "Domain Users", which should
be everyone in your domain.
Then, for all databases that the SQLServerPublic group should access:
USE Database
CREATE USER [MyDomain\SQLServerPublic] FOR LOGIN [MyDomain\SQLServerPublic]
In this way, you also avoid enabling the 'guest' user in your databases.
FWIW,
RLF
"Alain Trépanier" <AlainTrpanier@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C953689C-5716-4389-A00E-48C1D68099CB@xxxxxxxxxxxxxxxx
Hi,
In my company we are more than 10000 users. I want windows authentication
but I do not want to create 10000 login accounts. My server and databases
should allows all valid windows users (we use active directory).
I only allow the public role (of every db) to execute certain stored
procedures and/or UDF.
Within the stored procedures and/or UDF I avoid using dynamic SQL (exec)
and
I get the current user name via:
alter function dbo.ExecUserNt() returns varchar(101) as begin
declare @ix int;select @ix = CharIndex('\',SYSTEM_USER);
declare @execUserNt varchar(255);select @execUserNt =
SubString(SYSTEM_USER,@ix,1000);
if (Left(@execUserNt,1)='\') select
@execUserNt=SubString(@execUserNt,2,1000);
return @execUserNt;
end
Once I have it, I look-up in the central employee database for it's
logical
role then I can gives proper info/action in regard of the user e.g. a
given
user may see certain of its employee info, may approve vacations, etc..
How to achive that? (The one hop limitation of NTLM is not an issue in our
case)
Please,Please,Please,Please,Please,Please,Please,Please,Please,Please,Please
Thank you.
Alain Trepanier
- Follow-Ups:
- Re: Getting the windows username (of the connection)
- From: Erland Sommarskog
- Re: Getting the windows username (of the connection)
- From: Russell Fields
- Re: Getting the windows username (of the connection)
- Prev by Date: Re: SQL Sever Access Error
- Next by Date: Re: Getting the windows username (of the connection)
- Previous by thread: Re: SQL Sever Access Error
- Next by thread: Re: Getting the windows username (of the connection)
- Index(es):
Relevant Pages
|
Loading
