can I give SQL Server database name with hyphen like abc-123?

Question

I created a sql server database with name of abc-123, in that I created a table Emp, when I run like

select * from abc-123.emp;

I am getting the results. But when I am trying to grant some privilege to the user I unable to do that, getting syntax error near hyphen .

will any one help me?

Answer 1

Make sure you are escaping the names with [] (T-SQL) or "" (ANSI SQL). You are using non-standard naming.

-- Sample select
SELECT * FROM [abc-123].[dbo].[emp];
SELECT * FROM "abc-123"."dbo"."emp";

1 - Can you send me an example of the grant TSQL? If you are doing the action from SSMS, right click and script the code.

2 - Here is the link to the GRANT TSQL command. I do not see any syntax like you are trying.

http://technet.microsoft.com/en-us/library/ms188371.aspx

TO 'drupal'@'localhost' IDENTIFIED BY 'Drup@l';

First, it should be [drupal@localhost]. Second, I never seen the IDENTIFIED BY clause. Where are you getting that information from?

3 - Here is a quick TSQL script that creates a badly named database and user. If possible, change the name of the database and user.

Also, if you are granting permissions at the table level other than db_owner (very granular and a-lot of maintenance), then create an user defined database role. Add securables to the role and add your user to the role.

http://technet.microsoft.com/en-us/library/ms187936.aspx

Sample code.

-- Create new database
create database [abc-123]
go

-- Use new database
use [abc-123];
go

-- Create table from sample data
select 
       [BusinessEntityID]
      ,[PersonType]
      ,[NameStyle]
      ,[Title]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Suffix]
      ,[EmailPromotion]
      , cast([AdditionalContactInfo] as varchar(max)) 
        as [AdditionalContactInfoTxt]
      , cast([Demographics] as varchar(max)) 
        as [DemographicsTxt]
      ,[rowguid]
      ,[ModifiedDate]
into 
      [abc-123].[dbo].[emp]
from 
      AdventureWorks2012.Person.Person;

-- Create a login
CREATE LOGIN [drupal@localhost] WITH PASSWORD=N'Ja08n13$', DEFAULT_DATABASE=[abc-123]
GO

-- Create a user
CREATE USER [drupal@localhost] FOR LOGIN [drupal@localhost] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Add to database owner role 
EXEC sp_addrolemember 'db_owner', [drupal@localhost]
GO

Output with user in db_owner group.

Answer 2

Use Back Quote for the DB name

select * from `abc-123`.emp;

or, select the existing database with a USE statement and run the query.

USE `abc-123`;
select * from emp;