13

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?
21

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]
12

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 :-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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