SQL Server Security Model Changes

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