locked
How to define a role in T-SQL RRS feed

  • Question

  • Hello,

    My goal is to create a user-defined database-level role for a contained database.  I want that role to have the same permissions as db_owner, but without the ability to do anything with a single schema, or to the dbo user.  I think I've accomplished it via SSMS, by: setting the Owner to dbo, selecting db_owner as the Owned Schema, and on the Securables tab, adding the dbo user and setting all permissions to Deny, and adding the special schema and setting all permissions to Deny.

    First, does that set the permissions I've stated in my goal?

    Next, how do I encode this in T-SQL?  I think I understand how to set Deny for the dbo user and special schema, but I don't understand how to set the Owned Schema to db_owner.

    Thanks


    Uncaged

    Tuesday, July 4, 2017 1:51 AM

All replies

  • Hello,

    I've got a contained database, and I want to grant someone rights that are almost db_owner, with 2 exceptions: I don't want them to have any access to one schema, and I don't want them to be able to mess with my db_owner rights.  For the schema, I understand how to DENY that, but how do I create a role that can create, delete, and alter users and their permissions, but that can't delete or alter my user or my user's permissions?

    Thanks



    Uncaged

    Monday, July 3, 2017 8:40 PM
  • I don't think it can be done. Once you grant a user enough permission, the user can always elevate to db_owner if he wants to. You could set up a DDL trigger to prevent certain operations. But alas, triggers can always be disabled with DISBALE TRIGGER as this does not fire DDL triggers. Possibly you could DENY CONTROL on the DDL trigger to this role. But if you don't watch out, there could still be another hole.

    Monday, July 3, 2017 9:36 PM
  • I certainly wasn't thinking of actually making that user db_owner, unless I'd be able to deny them certain rights as needed.

    Uncaged

    Tuesday, July 4, 2017 12:32 AM
  • If you want to know what a certain UI in SSMS does in terms of T-SQL, use the Script button on the top of the form.

    Tuesday, July 4, 2017 8:24 AM
  • Hi Erland,

    Thanks.  What an unintuitive UI, since it only scripts the changes made to a role.  Meanwhile, the "Script Database Role as" only scripts the CREATE ROLE.  Lame.

    Regardless... I still don't understand what "Schemas owned by this role:" in the UI means, especially when its list includes the fixed roles.  What that generated was "ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [TestRole]".  What does that mean?

    My goal is to create a role that equals db_owner minus all rights of one schema and minus all rights to mess with the dbo user.  How, specifically, can I achieve that?

    Also, I want to be able to add a user to this role WITH GRANT, but I'm not clear how to do that.

    Thanks


    Uncaged

    Tuesday, July 4, 2017 2:36 PM
  • Thanks.  What an unintuitive UI, since it only scripts the changes made to a role.  Meanwhile, the "Script Database Role as" only scripts the CREATE ROLE.  Lame.

    Did I say that I never use this UI? You may guess why.

    Regardless... I still don't understand what "Schemas owned by this role:" in the UI means, especially when its list includes the fixed roles.  What that generated was "ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [TestRole]".  What does that mean?

    ALTER AUTHORIZATION changes the ownership of an object. So this statement makes TestRole the owner of the schema db_owner. (The schema db_owner exists only for legacy reasons, and you would normally not put any objects in it.

    My goal is to create a role that equals db_owner minus all rights of one schema and minus all rights to mess with the dbo user.  How, specifically, can I achieve that?

    I have already answered this: I don't think this can be done. You can throw in a couple of DENY, but there will be little to stop the role member from undoing them.

    Also, I want to be able to add a user to this role WITH GRANT, but I'm not clear how to do that.

    WITH GRANT is not an option with adding role member. It is an esoteric option when granting a permission which permits the user to grant the right to someone else.

    Tuesday, July 4, 2017 9:33 PM
  • Using the old fixed database role permissions is not very flexible, but the new (2005) permission system can do things like that. You could GRANT CONTROL on the database, and then DENY CONTROL on the schema. However you are playing with fire. For instance you didn't mention that you probably want to deny the ability to create new users and grant them a bunch of permissions, such as CONTROL on the database, including the forbidden schema. Then there is the issue of denying ALTER AUTHORIZATION so that the user can't change ownership of objects.

    You are better off, just planning the permissions that that role needs, and assigning them. See the permissions chart, at http://go.microsoft.com/fwlink/?LinkId=229142. It's a little more work (one time) but you have less chance of error.

     

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, July 5, 2017 5:04 PM
  • Oh thank you so much for your valuable contribution.  I would never have known to look at CREATE ROLE.  Thank you for responding to the title of my question, without wasting time actually reading my post.


    Uncaged

    Tuesday, July 11, 2017 5:08 PM