Would like to deny schema access to sql azure database owner (not db_owner role)
Once a sql azure database is created by a login, we would like to be able to stop this login from seeing data in the schemas. Row security can be used to hide the data but a user logged in with this login can turn the row security off and then get access to the data.
Anthony Widdowson commented
I mean the SQL admin user and the login who created the database which in our case is different. Once that login has created the database and created a few contained users within it including schema admins, we would like to prevent the database creator login from getting access to the schemas but we can't - it automatically is assigned as admin
Jakub Szymaszek - Microsoft commented
While there is no general single solution, Azure/SQL offers at this point, you might consider a combination of the following:
- Using Always Encrypted (aka.ms/AlwaysEncrypted) to encrypt sensitive column in your database. Always Encrypted ensures only a key holder can access the data encrypted in the database (DBAs and other admins cannot).
- Assuming that by "login" above, you are referring to the SQL server administrator login, we recommend that you use this login only for the initial database setup. While there is no way to disable SQL server administrator login (that one that uses SQL authentication), we know some customers choose to "lock it down" by periodically setting its password to a random value and having audit policy that and alerts that detect any access through that login.
- In general, you may consider using Azure Active Directory authentication for database admin access (please see https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure) and have the Azure AD administrator login mapped to an Azure AD group. The group could have no standing members - admins could be added temporarily when needed.
Amish Chauhan commented
Would be helpful
Wiliam Ferraciolli commented
Very useful for us. Well thinking
Sean Middleton commented
this is a great idea, something that would make life easier for many OPs teams