INF: Logins Aliased to Databases Users Cannot Be Viewed from SQL Enterprise Manager

• Microsoft SQL Server version 7.0

SUMMARY

In SQL 6.5, aliases from a login to a database user could be viewed in Enterprise Manager by double-clicking on that database user. However, SQL 7.0 Enterprise Manager provides no means to view the logins aliased to a particular database user. This behavior is correct due to the fact that SQL 7.0 replaces aliases with roles, maintaining aliases only for backward compatibility. All aliases should be dropped, security accounts for the aliased logins should be added to the database, and the security accounts should be assigned to database roles. For example:

sp_dropalias '<login>;'
go
sp_grantdbaccess '<login>', '<database security account>'
go
sp_addrolemember '<database role>', '<database security account>'
go 

MORE INFORMATION

This problem is most often seen after a server that uses aliases is upgraded to SQL 7.0.

Executing "sp_helpuser" shows the aliased logins in SQL 7.0.

NOTE: Aliased logins are stored in sysusers with a '\' at the beginning of the name and a status of 16.

Additional query words:

Keywords : kbSQLServ700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo