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.nameFROM master.dbo.syslogins L, sysusers UWHERE L.suid = U.suid

or

SELECT suser_name(suid) AS nameFROM sysusers

The queries below do the same job, but we rewrote them to use SIDs rather than SUIDs:

SELECT L.loginnameFROM master.dbo.syslogins L, sysusers U WHERE L.sid = U.sid

or

SELECT suser_sname(sid) AS nameFROM 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.