SQL Server 2005 - Sysxlogins

Question

Post reply

SQL Server 2005 - Sysxlogins

rajdba

Ten Centuries

Points: 1362
More actions
December 29, 2008 at 7:11 pm

#133458

Hi All,

I want to write a script which can work on both SQL 2000 and SQL 2005, the SQL 2000 Script is given below

select @@servername,b.name,a.name

from master.dbo.spt_values a, master.dbo.sysxlogins b

where a.low = 0

and a.type = 'SRV'

and b.srvid IS NULL

and a.number & b.xstatus = a.number

As you know Sysxlogins is not present in SQL Server 2005.

Please help.

Thanks in Advance.

Regards,

Raj

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

The syslogins view is the common view between them. Otherwise, you need to check the server version and have two distinct code blocks. I have different code blocks in a few of my management procedures that are common between servers where functionality is different between versions.

rajdba Ten Centuries Points: 1362 More actions · Answer 2

rajdba

Ten Centuries

Points: 1362

December 29, 2008 at 8:39 pm

#918734

Hi Jonathan,

Thanks for the reply, but from which table I can find xstatus in SQL Server 2005.

Regards,

Raj

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

I don't know of one. The syslogin view has a column for each of the server roles that is a bit field in both 2000 and 2005, so you don't have to do a join to find out what roles a login has been granted.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

MANU-J.

SSC-Dedicated

Points: 31126

December 30, 2008 at 3:53 am

#918905

What exactly you want to pull out using this code?

MJ