Query Performance (Level 3)

Query Performance (Level 3)
SQL Server 6.x SQL Server 2000
Queries could include a server user ID (SUID) without performance implications, as shown in the following table. Queries using SUIDs continue to run and produce the same results as in earlier versions of SQL Server. However, there is a severe performance penalty because SUIDs are no longer native to the new security design.

Consider removing all references to SUIDs and replacing with references to security identification numbers (SIDs) (as shown in the following table) to avoid degradation in query performance.


SQL Server 6.x SUID Replace with SQL Server 2000 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

Examples

A. Use SIDs and SUIDs to display login names of users in sysusers

This example shows SQL Server 6.x queries that displayed 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

Here are the queries rewritten 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
 
Show: