How to grant permission to a user in Azure SQL Server

Question

I'm a newbie on SQL, I'd like to know how to grant select and other permissions to a specify user in Azure Sql Server.

I'm trying to use AUMC to do this, I've created a new login as well as a new user test and grant all permissions I can select on AUMC. (for master database, I've assigned roles loginmanager and dbmanager to test, for other database, I've assigned permissions db_owner, db_securityadmin, db_accessadmin, db_backupoperator, db_ddladmin, db_datawriter, db_datareader, db_denydatawriter, db_denydatareader to test).

After the setting, I'm trying to login to the Azure SQL Server via ssms. The login is success, but I cannot find any tables on the database except the System Tables. And when I execute SELECT TOP 1 * FROM <a_table>, it returns The SELECT permission was denied on the object <a_table>, database <the database>, schema dbo.

Answer 1

The problem is likely that you are adding your test user to the db_denydatawriter and db_denydatareader roles. These roles prevent the user from reading or writing in the database, overriding the permissions granted by other roles like db_datawriter and db_datareader. Try removing your user from these two 'deny' roles.

For more information on the various database-level roles, see: https://msdn.microsoft.com/en-us/library/ms189121.aspx