May 1, 2008 at 2:43 pm
What is the best method of querying the master tables to retrieve a list of all logins and their associated statuses on an MS-SQL 2000 system?
Obviously, there are the master..syslogins view and master..sysxlogins table, but neither facility provides a clear indication of the account status, i.e. locked, expired, open, etc ...
I have not been able to find any description of master..sysxlogins.xstatus or the translation of its value into meaningful information for my purposes.
Thanks to all for any assistance.
Azureal
May 1, 2008 at 3:42 pm
Well,
I think I may have just answered my own questions. I found the following table showing the bit values for the xstatus field in sysxlogins (source: aspfree.com[/url]):
[font="System"]Table 2-4 shows the bit mappings for the xstatus column.
PurposeBitDescription
denylogin1Indicates whether the login account is permitted access to the server.
hasaccess2Indicates whether the login account is permitted access to the server.
isntname3Is the name a Windows NT account name?
isntgroup3Is the name a Windows NT group name?
isntuser4Is the account a Windows NT user account?
If bit 4 is 0 and bit 3 is 1, the account is a Windows NT group.
If bit 4 is 1 and bit 3 is 1, the account is a Windows NT user.
sysadmin5The rest of the bits indicate membership in system roles:
0 = not a member
1 = is a member
securityadmin6
serveradmin7
setupadmin8
processadmin9
diskadmin10
dbcreator11
bulkadmin12 [/font]
If this is the case, I can just use the master..syslogins view and hopefully ensure compatability with newer versions of the server.
Thanks
May 1, 2008 at 4:00 pm
You can see how the syslogins view translates xstatus by looking at the definition of the view. The MS sp_help_revlogin http://support.microsoft.com/kb/246133 uses it to duplicate logins transferred between servers.
Greg
May 1, 2008 at 9:59 pm
This is a SQL 2005 forum. There are SQL 2000 forums on this site also.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2008 at 1:54 am
....and in SQL 2005 the sys.syslogins provide all the informations in columns.....so no probs.....
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
May 2, 2008 at 5:58 am
Thanks folks,
Sorry for posting in the wrong forum.
May 2, 2008 at 6:08 am
You can have a select query with case of substring(status,bit no,1) to have the same result.............
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply