Access control permissions on Azure SQL database schema

0
votes

Please let me know if it is possible to restrict a user access to to a particular schema within a Azure SQL database. I know it is possible on an on-premise SQL database, however need to know if it is supported on Azure.

Couldn’t find a concrete article where it states that security/access-control can be configured across Azure SQL database schemas. This is the most closer I could got on anything about schema access on Azure:

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins#permissions

Also are there any restrictions on azure to query tables of a different schema on the same database. Specifically asking this as Azure doesn't support querying across databases just as it can be done on on-premise (though there is a workaround in elastic query).

1
sql-serverazuresecurityschema

1 Answers

2
votes

Yes, it is supported on Azure SQL - see here

From the docs: 

CREATE SCHEMA Sprockets AUTHORIZATION Annik  
    CREATE TABLE NineProngs (source int, cost int, partnumber int)  
    GRANT SELECT ON SCHEMA::Sprockets TO Mandar  
    DENY SELECT ON SCHEMA::Sprockets TO Prasanna;  
GO

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-schema?view=sql-server-2017