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