A common mistake, and one I make frequently myself is to use a string in place of an identifier, or vise-versa. So to start, let’s have some definitions, shall we?

String

a linear sequence of characters, words, or other data.

Identifier

a sequence of characters used to identify or refer to a program or an element, such as a variable or a set of data, within it.

And because I always find examples fairly useful.

CREATE LOGIN [this is an identifier] 
WITH PASSWORD = 'this is a string';  
GO

You’ll notice that both identifiers and strings are a sequence of characters. The big difference being that one is a place to contain data (string) and the other is the name of an object (identifier). In fact identifiers are strings that identify the names of objects. Which is, of course, one of the sources of confusion. It helps though that within SQL Server (actually, most if not all programming languages) strings are a bit more strongly defined.

A string within SQL Server is still a sequence of characters but it must be delimited with single (or depending on the settings double) quotes. An identifier, on the other hand, may or may not have any delimiters around it depending on what the name is. Identifiers with spaces, special characters or emojis require delimiters, otherwise it’s optional. If it does you will use square brackets ([]) or double quotes (again depending on that setting).

Now, if you look at the CREATE LOGIN statement again. You’ll notice that the object (well technically principal) name is an identifier while the password is a string. So when you need to reference or name a new table, column, schema, database, stored procedure, function, credential, login, user, role, etc, etc you are using an identifier. Cases where you are using a path, a password, putting data into a character column or variable then you are using a string.

You wouldn’t believe how often I end up using a string for that login name. Hopefully having laid it out now I’ll cut back on that particular mistake some.