Hyphen in my computer's name causing T-SQL error

Question

I’m a developer, not a DBA (which is showing, I’m afraid). I am trying to run Report Builder 3.0 with SQL Server 2014 Express on my home computer (named John-PC) and cannot run my reports.

I accidentally created a user/login combo of user = John-PC and login = John-PC\John. When I try to delete the entry with:

Drop Login John-PC\John

I get an error:

Incorrect syntax near '-'.

I think the problem is the hyphen in my computer's name.

1. Is there a way to get around the syntax error?
2. Is there another way to change or drop the user (I tried dropping from sys.server_principals but received an error that I couldn’t make ad hoc changes).
3. Can I give Report Builder a new user/login name somehow?
4. If none of the above, can I change the name of my computer to John_PC or will that create a host of other problems that I can’t even imagine?

Answer 1

When you have special characters in a name put []'s around it to let SQL know that it's an identifier. This is also how you manage special characters.

So in your case

Drop Login [John-PC\John]

Answer 2

If you just have one login, then manually quoting it with [ ] will work. If you have many of them then you have to build a dynamic sql like below to progrmatically get the drop login [login_to_drop] from sys.server_principals using QUOTENAME() tsql

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql+= N'DROP LOGIN ' + QUOTENAME(name) + ';' 
FROM sys.server_principals 
WHERE name <> N'sa' -- do not drop SA
AND name NOT LIKE N'##%' -- special logins 
AND name NOT LIKE N'NT [AS]%' -- special logins NT related
AND [type] IN ('S', 'U', 'G','R') -- S = SQL login | U = Windows login | G = Windows group | R = Server role
AND principal_id > 256
--AND name in () -- Filter to drop specific logins
AND name <> SUSER_SNAME(); -- This will avoid yourself for being dropped !
PRINT @sql;
-- once you verify that below logins will be dropped, 
-- uncomment below line 
-- EXEC master.sys.sp_executesql @sql;

Lesson learned, dont use special characters .. else be prepared to deal with them with some pain :-)