Can I provide a SQL Server database name with hyphens like abc-123?

Question

I created a sql server database called abc-123 in which I created an Emp table when I run as

 select * from abc-123.emp; 

I get the results. But when I try to provide some privileges to the user, I can not do this, get a syntax error near the hyphen.

Anyone help me?

Answer 1

Make sure you avoid names using [] (T-SQL) or "" (ANSI SQL). You are using custom naming.

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

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

2 - Here is a link to the GRANT TSQL command. I do not see the syntax as you try.

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

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

First, it should be [ drupal@localhost ] . Secondly, I have never seen the IDENTIFIED BY . Where do you get this information from?

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

Also, if you grant permissions at a table level other than db_owner (very verbose and multivolume), then create a user-defined database role. Add securables to the role and add the 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 

The output with the user in the db_owner group.

Answer 2

Use [] around the database name:

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

OR

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

Answer 3

if you use the database_name with the table name, then suppose you also specify the name schema .

 select * from [abc-123].dbo.emp