我可以用 abc-123 这样的连字符给 SQL Server 数据库名称吗? [英] can I give SQL Server database name with hyphen like abc-123?

问题描述

我创建了一个名为 abc-123 的 sql server 数据库，因为我创建了一个表 Emp，当我运行 like

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 .

有人会帮我吗?

推荐答案

确保使用 [] (T-SQL) 或 "" (ANSI SQL) 对名称进行转义.您使用的是非标准命名.

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 - 你能给我发一个授权 TSQL 的例子吗?如果您从 SSMS 执行操作，请右键单击并编写代码脚本.

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 - 这是 GRANT TSQL 命令的链接.我没有看到您尝试的任何语法.

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';

首先，它应该是[drupal@localhost].其次，我从未见过 IDENTIFIED BY 子句.你从哪里得到这些信息?

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

3 - 这是一个快速的 TSQL 脚本，用于创建名称错误的数据库和用户.如果可能，请更改数据库和用户的名称.

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.

此外，如果您在 db_owner 以外的表级别授予权限(非常精细且需要大量维护)，请创建用户定义的数据库角色.将安全对象添加到角色并将您的用户添加到角色.

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

示例代码.

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

使用 db_owner 组中的用户输出.

Output with user in db_owner group.

这篇关于我可以用 abc-123 这样的连字符给 SQL Server 数据库名称吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！