 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Security / January 2009Getting the windows username (of the connection)
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 Just enable the guest server account via GRANT CONNECT TO guest; > Hi, > [quoted text clipped - 28 lines] > Thank you. > Alain Trepanier 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 > Hi, > [quoted text clipped - 31 lines] > Thank you. > Alain Trepanier 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! > Alain, > [quoted text clipped - 52 lines] > > Thank you. > > Alain Trepanier Alain, If a login accesses the SQL Server because it is a member of a group that was granted connect rights, it is still know by its login name, not by the group name. For example: MyDomain\SQLServerPublic Members: MyDomain\George MyDomain\Helen When George logs into the server his SYSTEM_USER will be "MyDomain\George" and will not be "MyDomain\SQLServerPublic". Likewise, Helen will be "MyDomain\Helen". You can easily take a backup from SQL Server 2000 and restore it to SQL Server 2005 or SQL Server 2008. That works fine. However, once a database has been restore to a higher version SQL Server (no matter what compatibility level the database is set to) the physical file structure is updated to match the server version. That means that it is _not_ possible to back up a database from SQL Server 2005 and restore it to SQL Server 2000. RLF > Will I be able to obtain the effective user nt username and domain name > using [quoted text clipped - 87 lines] >> > Thank you. >> > Alain Trepanier Thank you very much for your advises! > Alain, > [quoted text clipped - 112 lines] > >> > Thank you. > >> > Alain Trepanier Is there any good doc or white papers or tutorials on how one uses this domain group approach? Thanks. Josh > Alain, > [quoted text clipped - 112 lines] > >> > Thank you. > >> > Alain Trepanier JRStern, This paper helped me a few years ago. It is for SQL Server 2000, but the principles have not changed. http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec01.mspx#E1C I found one reference that implies that it is also discussed in the book (which I have not read): "MCTS Self-Paced Training Kit (Exam 70-431): Microsoft SQL Server 2005 Implementation and Maintenance" by Solid Quality Learning. http://www.amazon.com/gp/product/073562271X/sr=1-1/qid=1156801743/ref=sr_1_1/104 -6313703-3867159?ie=UTF8&s=books RLF > Is there any good doc or white papers or tutorials on how one uses this > domain group approach? Thanks. [quoted text clipped - 131 lines] >> >> > Thank you. >> >> > Alain Trepanier > 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 [quoted text clipped - 3 lines] > 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. A Russell said, you can easily to this. Beware though that the there may be things that do not work any more because of behavioural changes. The most important as I recall: o Old-style outer-join *= and =* are not accepted. o SELECTs from wiews that have TOP 100 PERCENT and ORDER BY are far less likely to return data by the ORDER BY clause when there is no ORDER BY clause in the query itself. o Locking and index hints now require the WITH keyword, for instance WITH (INDEX = my_index). o SQL 2000 permitted you to use bogus column prefixes in ORDER BY clauses. This has been fixed in SQL 2005.  SignatureErland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.