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

Question

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?

Answer 1

This will not work on sql azure. You will need to grant view definition at the database level. (without the ANY keyword)

GRANT VIEW DEFINITION TO gu6t6rdb

P.S: I hit the exact same issue and this seemed to solve my problem. I also had to do a Grant Execute (but it depends on what your bacpac is applying to the database)

Answer 2

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'