Changes that Microsoft made to the SQL Server security model have caused performance slowdowns for queries that use SQL Server 6.5 system functions to access a server user ID (SUID). For example, the following code shows a SQL Server 6.x query that displays the login names of all users in sysusers:
SELECT L.name FROM master.dbo.syslogins L, sysusers U WHERE L.suid = U.suid
or
SELECT suser_name(suid) AS name FROM sysusers
The queries below do the same job, but we rewrote them to use SIDs rather than SUIDs:
SELECT L.loginname FROM master.dbo.syslogins L, sysusers U WHERE L.sid = U.sid
or
SELECT suser_sname(sid) AS name FROM sysusers
SQL Server Books Online (BOL) goes so far as to call the slowdown "a severe performance penalty," and our experience backs that claim. The following chart from SQL Server Books Online shows the affected SQL Server 6.5 functions and columns, and the chart lists the changes that BOL recommends you make for SQL Server 7.0 queries. Keep in mind that the old queries will still run, but they might run very, very slowly and consume surprising amounts of CPU resources, even on a powerful SMP machine.
SQL Server 6.x SUID | Replace with SQL Server 7.0 SID |
SUSER_ID | SUSER_SID, which returns a SID |
SUSER_NAME | SUSER_SNAME, which accepts a SID as input |
syslogins.suid | syslogins.sid |
sysdatabases.suid | sysdatabases.sid |
sysremotelogins.suid | sysremotelogins.sid |
sysusers.suid | sysusers.sid |
sysalternates.suid | sysusers.isaliased |
sysalternates.altsuid | sysusers.isaliased |