Red Gate forums :: View topic - Detection of Logins should use SUSER_ID
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

Detection of Logins should use SUSER_ID

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
shawnC



Joined: 22 Nov 2011
Posts: 10

PostPosted: Thu Nov 14, 2013 12:42 pm    Post subject: Detection of Logins should use SUSER_ID Reply with quote

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

Code:
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:
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
_________________
http://stackexchange.com/users/13638/gbn
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6589

PostPosted: Mon Nov 18, 2013 3:27 pm    Post subject: Reply with quote

Thanks for your suggestion. Can you please add this to the suggestions page at http://redgate.uservoice.com/forums/141379-sql-compare-feature-suggestions ?

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

Thanks!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group