** 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 Friday, August 28, 2015 8:49 PM