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
Thanks for your suggestion. Can you please add this to the suggestions page at http://redgate.uservoice.com/forums/141 ... uggestions ?
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
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