I am trying to create a contained user for a database in SQL Azure that only as read-only access. What I am doing is:
- Connecting to the database via SSMS using my admin account.
- Right click the database in question and select new query.
Then I typed the following:
CREATE USER DEMO_dbreader WITH PASSWORD = 'pass@word1'
EXEC sp_addrolemember N'db_datareader', N'DEMO_dbreader'`
After that I disconnect from the database in SSMS and I reconnect using the new SQL account I just created. then I type in the following as a test:
CREATE table testing123 (id int)
To my surprise, I can create tables (!?!) I can also truncate them and drop them (?!?) Not sure what the heck is going on here. Am I missing something?
I need the user to be contained in this database and not to exist outside of it.