Restrict contained SQL Azure user to read-only access

Question

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:

1. Connecting to the database via SSMS using my admin account.
2. 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.

Answer 1

This should never happen. db_datareader will grant only select access to the database.

I used your query in my Azure database and I couldn't create a table, the user got only select access to the database.

Either the user got elevated access or else you might be logged in with the administrator account. Do the same test in a different user database (just to ensure the user didn't get created already) and then connect using the user name and run the query below.

SELECT DatabaseName=DB_NAME(),
 UserName=USER_NAME(),
 x.DoesUserHasAccess,
 a.entity_name,
 a.permission_name
FROM sys.fn_my_permissions(NULL, 'DATABASE') a
 CROSS APPLY
 (
 SELECT DoesUserHasAccess = HAS_PERMS_BY_NAME(
 DB_NAME(),
 a.entity_name,
 a.permission_name
 )
 ) x
WHERE x.DoesUserHasAccess = 1;

When I tested for the same user it returned only select access. enter image description here

• pmdci: I basically had to retrace my steps and do it again. Clearly there was something wrong in the steps we were taking. The idea of tests helped!