Options
Detection of Logins should use SUSER_ID
shawnC
Posts: 17
The code generated to test for an existing SQL login is this
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
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
Comments
Suggestions on the uservoice forum are weighted by the number of people who also want the same enhancement.
Thanks!