0

I am trying to run the following in SSMS:

 IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins 
           WHERE name = 'test.'user')  
BEGIN
    CREATE LOGIN [test.'user] 
         WITH PASSWORD = 'test'123', 
         DEFAULT_DATABASE = TestDB, 
         DEFAULT_LANGUAGE = [us_english], 
         CHECK_EXPIRATION = OFF,  
         CHECK_POLICY = OFF  
 END

It gives an error:

   Unclosed quotation mark after the character string.

This is because the password has a single quote. How can i escape the password and username to fix this ?

Sarah
  • 21
  • 5
  • dont i need to set the password first , how do i set the password ? – Sarah Mar 23 '23 at 05:33
  • Will this work ? Escaping those with another single quotes ? `IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'test''user') BEGIN CREATE LOGIN [test''user] WITH PASSWORD = 'test''123', ` – Tushar Mar 23 '23 at 05:54
  • that will work , but the sql is generated from c# and i wont know exactly where the ' is . Also what if they use other characters ? So looking to escape the string before it comes to sql – Sarah Mar 23 '23 at 06:02
  • Try this: IF NOT EXISTS (SELECT loginname FROM master.dbo.syslogins WHERE name = 'test.user') BEGIN CREATE LOGIN sellis WITH PASSWORD = 'test123', DEFAULT_DATABASE = TestDB, DEFAULT_LANGUAGE = [us_english], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF END – S. Ellis Mar 24 '23 at 15:34

0 Answers0