Rick Byham has a WIKI post showing the fixed server and fixed database roles and how they map. You can look here: http://social.technet.microsoft.com/wiki/contents/articles/database-engine-fixed-server-and-fixed-database-roles.aspx
The chart shows that db_datareader role is identical to GRANT SELECT ON [database]. So it is still fine to use, but the recommendation is to move away from those roles to the more granular commands. Some of the other fixed database roles are less clearly defined for most people. Using the explicit commands results in greater clarity when reporting rights.
Obviously you know how to grant finer grained permissions. I am trying to break loose from the old roles whenever possible, but db_owner (for example) is a hard habit to break.
Not sure where you've stumbled along the way, but this works for me:
CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
USE somedatabase;
GO
CREATE USER permtest FROM LOGIN permtest;
GO
According to this page, the user needs SELECT
permission on sys.sql_expression_dependencies
, and VIEW DEFINITION
on the database.
In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don't have the ability to view definition (which includes dependency chains):
GRANT SELECT ON sys.sql_expression_dependencies TO permtest;
In order to actually see any relationships in somedatabase
, I also had to add the following:
GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;
I could not find any way to make that more granular (VIEW
/DENY
definition worked for individual objects, but without the database-level right, I still couldn't see any rows in the catalog view, and DENY
did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway - if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?
If you don't want to grant VIEW DEFINITION
on the database, then create procedures that use EXECUTE AS OWNER
, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.
CREATE PROCEDURE dbo.GetDependencies
WITH EXECUTE AS OWNER
AS
BEGIN
SELECT is_schema_bound_reference --, ...
FROM sys.sql_expression_dependencies;
END
GO
GRANT EXECUTE ON dbo.GetDependencies TO permtest;
Best Answer
If we look at the SQL Server Permissions Poster (2017 edition):
It appears as though
ADMINISTER BULK OPERATIONS
covers bulkadmin in addition toOPENROWSET
which allows access to external sources.