tSQL在SQL Azure上设置具有视图定义权限的用户 [英] tSQL to set up user with View Definition permission on SQL Azure

问题描述

我正在尝试使用Azure门户将SQL Azure数据库导出到.bacpac文件.我数据库上的管理员用户名包含*.当我在用户名字段中使用它时，出现此错误.

I'm trying to export a SQL Azure database to a .bacpac file using the Azure portal. The administrator username on my database contains a *. When I use it in the username field I get this error.

The login name must meet the following requirements:
It must be a SQL Identifier.
It cannot be a system name, for example:
- admin, administrator, sa, root, dbmanager, loginmanager, etc.
- Built-in database user or role like dbo, guest, public, etc. 
It cannot contain:
 - White space like spaces, tabs, or returns
 - Unicode characters
 - Nonalphabetic characters ("|:*?\/#&;,%=)
It cannot begin with:
 - Digits (0 through 9)
 - @, $, +

因此，我使用以下tSQL将新用户添加到数据库中.

So I add a new user to the database using the following tSQL.

USE master;
CREATE LOGIN gu6t6rdb WITH PASSWORD = 'kjucuejcj753jc8j'

USE MyActualDB;
CREATE USER gu6t6rdb FOR LOGIN gu6t6rdb

门户网站导出表单接受该用户名，但随后出现以下消息并出错.

The portal export form accepts that username but later errors with the following message.

服务操作期间遇到错误.无法提取 指定数据库中的软件包.逆向工程操作 无法继续，因为您没有查看定义"权限 "MyActualDB"数据库.

Error encountered during the service operation. Could not extract package from specified database. The reverse engineering operation cannot continue because you do not have View Definition permission on the 'MyActualDB' database.

要解决此问题，我尝试了以下tSQL

To fix this I tried the following tSQL

GRANT VIEW ANY DEFINITION TO gu6t6rdb 

这将引发以下错误

此版本的SQL Server不支持安全类'server'

Securable class 'server' not supported in this version of SQL Server

我应该如何使用tSQL在数据库上提供其他用户，并赋予该用户足够的特权，以通过Azure门户将数据库导出到Azure blobstore中的.bacpac文件?

How should I use tSQL to provide an additional user on my database and give the user sufficient privileges to export the database through the Azure portal to a .bacpac file in an Azure blobstore?

推荐答案

知道了.我可以将用户添加到db_owner角色，然后导出将继续进行而不会出现错误.

Got it. I can add the user to the db_owner role and then the export proceeds without error.

EXEC sp_addrolemember 'db_owner', 'gu6t6rdb'

这篇关于tSQL在SQL Azure上设置具有视图定义权限的用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！