** I believe this issue was caused by a "bug" in Azure SQL Authentication.

Originally I assigned MyUser the assword 'MJqEhjkIA#&&Y3_VRUkE!_'  and while there were no compalints from the sql routines the symptoms described below plagued me. When I changed the password to a "less complex" one, the same scripts returned the expected results.

**

I created an Azure SQL DB, v12. I want to create a "Login\User" "MyUser" with "dbo" permissions to "MyDB" and nothing else. I ran the following script.

1. Connect to the master DB in SSMS and run this script

CREATE LOGIN MyUser WITH password = '12345

CREATE USER MyUser FROM LOGIN MyUser  with DEFAULT_SCHEMA=[dbo]

2. Connect to the MyDB DB and run this script

CREATE USER MyUser FROM LOGIN MyUser with DEFAULT_SCHEMA=[dbo]

EXEC sp_addrolemember 'db_owner', 'MyUser'

I found that MyUser has access to MyDB but can not see any tables in it.  (The admin account used to create the Azure SQl Database can see the tables)

Any help appreciated

TIA,

edm2

P.S. I also found that after running the scripts that MyUser appeared in database security for DBs other than master and MyDB!

• Edited by edm2 10 hours 20 minutes ago

I've been unable to repro. When you connect as MyUser, can you verify that the following query includes a row for "db_owner | ROLE | GRANT OR DENY"?

SELECT name, type, usage FROM sys.user_token ORDER BY usage, type, name

If db_owner is included, then the user should be able to see all tables. 

On a separate note, we typically recommend you use contained users on Azure SQL DB rather than the traditional login-user model. Contained users remove an extra round-trip to master and thus yield better performance. More info here: https://msdn.microsoft.com/library/ff929188.aspx