Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The recommendations I have come across advise that single quotes in SQL Server should be escaped using two single quotes. Thus Ravi's becomes Ravi''s.

Another alternative is to use parameters.

In most of the examples I have seen the words usually have only one quote. Most times it works but I have been unable to make it work while creating a database login.

CREATE LOGIN Ng''ang''a  WITH PASSWORD  'Ng''0ng''0' 

Where I am going wrong?

share|improve this question

migrated from stackoverflow.com Feb 28 '12 at 22:19

This question came from our site for professional and enthusiast programmers.

1  
I think your missing quotes around your user name. –  asawyer Feb 28 '12 at 21:25
    
create login [Ng'ang'a] Surround your login name with square brackets. –  Thomas Stringer Feb 28 '12 at 21:28
    
Try: CREATE LOGIN [Ng'ang'a] WITH PASSWORD = 'Ng''0ng''0' –  Pasha Immortals Feb 28 '12 at 21:35
3  
Why does you login need to have single quotes in it? While it is certainly allowed to do that, I think you should question it as you're going to run into other problems after you've created it. –  Aaron Bertrand Feb 28 '12 at 21:43
    
@AaronBertrand can you imagine escaping the connection string? –  swasheck Sep 10 '12 at 16:39

1 Answer 1

The user name is an ID/QUOTED ID that should be enclosed in double quotes or square brackets while the password is a regular string where single quotes need to be doubled. The correct syntax would in other words be;

CREATE LOGIN "Ng'ang'a" WITH PASSWORD='Ng''0ng''0'

or

CREATE LOGIN [Ng'ang'a] WITH PASSWORD='Ng''0ng''0'
share|improve this answer
    
double quotes will only work if QUOTED_IDENTIFIER is ON but +1 for explaining why the user name is different than the password –  Conrad Frix Feb 28 '12 at 21:35
4  
@Conrad Double quotes are also really hard to read and confuse new users who aren't sure if they're strings or identifiers. But creating a login with an apostrophe is not exactly a good idea in the first place. –  Aaron Bertrand Feb 28 '12 at 21:37
    
@ConradFrix Did not know that since it seems to be turned on by default, thanks! –  Joachim Isaksson Feb 28 '12 at 21:37
    
@AaronBertrand The user wants to use his actual name which has single quotes. –  CallMeKags Feb 29 '12 at 17:07
    
@CallMeKags I didn't say there isn't a reason, I just said it isn't a good idea. As I suggested above, this is not the last time you're going to have an issue with this. –  Aaron Bertrand Feb 29 '12 at 17:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.