CODEWITHSUNDEEP
sql-serverazuresecurityschema
Unni Kris
Unni Kris

Reputation: 3105

Access control permissions on Azure SQL database schema

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://learn.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).

Upvotes: 0

Views: 2515

Answers (1)

Murray Foxcroft
Murray Foxcroft

Reputation: 13795

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://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-schema?view=sql-server-2017

Upvotes: 2

Related Questions