Detection of Logins should use SUSER_ID

shawnC

The code generated to test for an existing SQL login is this

IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'xxx')
CREATE LOGIN [xxx] WITH PASSWORD = 'p@ssw0rd'

This relies on a higher privilege (sysadmin, securityadmin or ALTER ANY LOGIN) to work correctly because of "Metadata visibility". I don't want to give developers or my CI suite these permissions.

However, this will work regardless

IF SUSER_ID(N'xxx')) IS NULL
CREATE LOGIN [xxx] WITH PASSWORD = 'p@ssw0rd'

SUSER_ID was changed in SQL Server 2005 to work with sys.server_principals.

So, can SQL Compare be fixed please

Bonus point: syslogins is actually a SQL Server 7.0 system table that has been a view for every later version. It was replaced by sys.sql_logins in SQL Server 2005

Brian Donahue

Thanks for your suggestion. Can you please add this to the suggestions page at http://redgate.uservoice.com/forums/141 ... uggestions ?

Suggestions on the uservoice forum are weighted by the number of people who also want the same enhancement.

Thanks!