tSQL to set up user with View Definition permission on SQL Azure

azure, azure-sql-database, sql, sql-server, t-sql

Solution

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'

Problem

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) - @, $, + ``` 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. 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. To fix this I tried the following tSQL ``` GRANT VIEW ANY DEFINITION TO gu6t6rdb ``` which throws the following error Securable class 'server' not supported in this version of SQL Server 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?

Original source