Skip navigation

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:

FROM master.dbo.syslogins L, sysusers U
WHERE L.suid = U.suid


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


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
Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.