Detection of Logins should use SUSER_ID

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

Detection of Logins should use SUSER_ID

Postby shawnC » Thu Nov 14, 2013 12:42 pm

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

Code: Select all
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

Code: Select all
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
shawnC
 
Posts: 13
Joined: Tue Nov 22, 2011 1:53 pm

Postby Brian Donahue » Mon Nov 18, 2013 3:27 pm

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!
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 1 guest