Since SQL Server permissions work as "deny first", I was wondering if that applies downward to individual permissions that are granted, if the parent permission isn't granted e.g. db_ddladmin is not granted but CREATE VIEW is for the Login.

  • you can just try this out and post the results :-) – Kin Shah Nov 1 '19 at 17:12
  • @KinShah True, but in lieu of setting up my own development server at the moment, let's pretend I don't have access to set permissions. Any chance you know if my assumption is correct? :) – J.D. Nov 1 '19 at 17:39
  • 1
    Msg 2760, Level 16, State 1, Procedure MyView, Line 1 [Batch Start Line 0] The specified schema name "dbo" either does not exist or you do not have permission to use it. SQL2016 Dev – Kevin3NF Nov 1 '19 at 18:26
  • 1
    db_ddladmin is not a "parent permission" it's a legacy role from the early days of SQL Server. CREATE VIEW's "parent permission" is ALTER on the DATABASE. To create a view a user requires the CREATE VIEW database permission and ALTER on the target schema. – David Browne - Microsoft Nov 2 '19 at 14:15
  • @DavidBrowne-Microsoft Is it Microsoft's recommendation to not use those predefined roles available in the User Mapping GUI when setting database permissions for a login? If so, what's the alternative that is recommended?... creating custom roles? – J.D. Nov 3 '19 at 23:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.