September 23, 2002 at 3:26 pm
I've been charged with switching our security mode from mixed to NT only. We have hundreds of users, and most but not all have both NT and SQL logins. I am trying to figure out a way to determine which users have SQL logins but not NT logins to the db, and I wrote the following query:
select a.name
from sysusers a
where left(a.name, 7) = 'DOMAIN\' and
substring(a.name, 9, 50) = (select b.name
from sysusers b
where b.name = a.name)
This returns 0 rows, although I have manually found at least 3 users who have no NT login to the server. I know I am missing something easy, I just can't figure it out. Any suggestions?
September 23, 2002 at 3:53 pm
First I think I would consider using the SYSLOGINS table in master. Here is a query that might work for you. Let me know how it goes.
select loginname
from master..syslogins a
where
a.loginname not in (select substring(b.loginname, charindex('\',b.loginname)+1, 50)
from master..syslogins b
where charindex('\',b.loginname) > 0 )
and
charindex('\',a.loginname) = 0
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
September 23, 2002 at 5:46 pm
You dont have instances where users get permissions based on an NT group rather than their actual login?
Andy
September 24, 2002 at 12:35 pm
quote:
You dont have instances where users get permissions based on an NT group rather than their actual login?Andy
http://www.sqlservercentral.com/columnists/awarren/
Yes there are some instances of NT group permissions, but it is not universal; that is my task. Currently, I don't have access to the Active Directory, so I'm not sure how it's laid out yet (the joys of a new assignment).
September 24, 2002 at 5:54 pm
Seems to me you're going at it the wrong way. Why not identify who needs access and if you have users with different access needs. Normally you do have some different roles but nine times out of ten it's handled by the app, not by the database. Once you know who is authorized what access just have your network admin build groups to match the roles (they can then drop other groups and/or users in that container). Add the groups as logins, create roles, add the logins to the db and to the appropriate role(s). Delete the sql logins from the db, on to the next one! If you have one NT login per person you're just trading one pain for another. NT is better in that you have lockout after max tried, expiration, etc, but you're still in the admin business, having to add/delete users all day long. Let the network dude handle it!
Andy
September 25, 2002 at 8:21 am
Thanks for the tip. I'll give it a shot.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply