I'd like to build a bunch of new databases (several dozen) in SQL Server 2016. However, I'd like all users to access a single database (called SECURITY for now), and apply security to that one database, where each user would be given access to a single schema for their department. Does SQL Server 2016 allow inheritance of security, so you don't have to build those users in each database, and apply the same security in each? Some of these schemas will just have views in which the definitions that read schemas and tables in other databases.
A few of these schemas in DEV_SECURITY will allow read/write and drop/create on views, tables, and stored procedures, for SECURITY, but also one other database.
Does this require me to build out these users, and their security in all databases, or can I simplify by just building them in SECURITY?
Best Answer
What you are asking about is Ownership Chains.
In the simple case when we are within one database, when you give permissions on a view and the view is owned by the same principal that owns all the underlying tables, you just do nothing: a user will access the view without any permission on the underying tables.
In your case there are more than one database, and we are talking about Cross-Database Ownership Chaining
You should read this paragraph in the article above to understand Potential Threats in case youenable crossdb ownership chains.
You can read more on it here: Enabling Cross-Database Access in SQL Server.
In brief, to make it work, in addition to the requirement to have the same owner you should enable both database to db chainig:
ALTER DATABASE ... SET DB_CHAINING ON.In this case you still can grant permissions only on your views without granting them on other database's tables, but you still should map your users in all databases of interest. Otherwise when accessing tables from other databases through view ownership chain breaks when you are leaving the database and you should give the permissions to all the underlying tables.