Scripting (NOT X-FER''ING) logins in SQL 2005....

• tc9d

  SSC Enthusiast

  Points: 169

  More actions

  October 31, 2006 at 11:27 am

  #170770

  We're in the process of converting our custom SQL "build" from SQL2000 to SQL2005 and I've come to a point where I'm a bit lost.  In SQL2000 we have a scheduled job that runs weekly on each SQL Server that scripts out all local logins, passwords, database & role memberships, etc.  This is very helpful for moving databases because we have it structured so that the password is scripted (for std. users) and we can then run the resultset on the destination SQL Server and not have to deal w/passwords. 

  One of the key queries in this process is:

  SELECT

  sid, name, xstatus, password FROM master..sysxlogins

  In trying to convert this capability to SQL2005, I see that there is no longer a sysxlogins table in master.  There is a sysxlgns table, but I can't seem to query it.  If I run "select * from master.sys.sysxlgns" I get:  Msg 208, Level 16, State 1, Line 3

  Invalid object name 'master.sys.sysxlgns'.

  If I run sp_help in master, it clearly shows up as a system table.  Are we no longer allowed to query system tables in SQL2005?  If not, would anyone know where to get the data from the first query above.  A system database diagram for SQL2005 would be most helpful also.  I'm sure BOL has that, but I just haven't tracked it down yet.

  Any help would be greatly appreciated.

  Thanks in advace,

  Dave

   

• lucky-80472

  SSCertifiable

  Points: 7793

  More actions

  October 31, 2006 at 12:59 pm

  #669057

  Hello,

  I'm able to query the system table through the query provided by you.

  SELECT sid, name, xstatus, password FROM master..sysxlogins

  there is no need to prefix "sys"

  Hope this helps.

  Thanks

   

  
  Lucky

• Yelena Varshal

  SSC-Dedicated

  Points: 34286

  More actions

  October 31, 2006 at 1:03 pm

  #669058

  He was asking about another table. There is no 'o' or 'i' in the name and the ID of this object is 42 if you query sysobjects.

  select

  * from master.sys.sysxlgns

  Dave, you can still query syslogins as Lucky says and also a view:

  select

  * from sys.sql_logins

  Regards,Yelena Varsha

• tc9d

  SSC Enthusiast

  Points: 169

  More actions

  November 1, 2006 at 9:15 am

  #669239

  Since I posted this yesterday, I found that MS has already revamped this capability for SQL2005 - http://support.microsoft.com/kb/918992/.

  Thanks for the responses!

  Dave