Query help requested for Login information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query help requested for Login information

Author

Topic

solart
Posting Yak Master

148 Posts

Posted - 2002-09-26 : 16:38:39

I have the following query:

use database
go
select master..syslogins.name as login_name,
sysusers.name as user_name
from master..syslogins inner join sysusers
on master..syslogins.sid = sysusers.sid
order by 1

/* use suid above for SQL 7 */
/* use sid above for SQL 2000 */

which produces a list of logins and their associated user names for the specified database.

I would like to add a column which would tell if the login is a "standard" login, or a "windows NT" login, or a "windows group" login.

It seems I recall some properties like "isnt", etc., but don't remember how to get to them.

Help would be appreciated!!

TIA solart

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-26 : 17:49:24

Look up syslogins in Books Online, it details the "isnt..." columns.

solart
Posting Yak Master

148 Posts

Posted - 2002-09-27 : 13:08:14

Thanks Rob. Your efforts are appreciated!! What I ended up with was the following:

use itscat -- <----modify database name as appropriate
go
select l.name as login_name,
s.name as user_name,
case
when (s.isntgroup = 1) then 'NT Group'
when (s.isntuser = 1) then 'NT User'
when (s.issqluser = 1) then 'SQL User'
when (s.issqlrole = 1) then 'SQL Role'
when (s.isapprole = 1) then 'App Role'
else '????'
end
as User_Type,
l.dbname as 'Default DB',
l.language as 'Default Language'
from master..syslogins l inner join sysusers s on l.sid = s.sid
order by 1

/* use suid above for SQL 7 */
/* use sid above for SQL 2000 */

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources