Grant/Deny properties of Role

SQL Server Administration
1

I ran the following code

CREATE ROLE db_TESTRole AUTHORIZATION [dbo]

GRANT EXECUTE TO db_TESTRole

DENY VIEW DEFINITION TO db_TESTRole

How come I don't see it getting selected when I review the properties of the role?

image
image930×812 15.1 KB

2

VIEW DEFINITION was not previously granted - so a DENY doesn't modify the permissions. You would have to grant that permission - and then deny the permission to see it in Securables.

As for your EXECUTE - you haven't granted EXECUTE to anything so there is nothing granted. It should be: GRANT EXECUTE ON {object} TO {role}; - ex: GRANT EXECUTE ON schema::dbo TO db_TESTRole;

1 Like
3

Thank you for the explanation Jeff.

Then what is the purpose of this when it is not granting to anything? When I ran it, it returned no errors. Does it mean that the role can execute everything? Also, how would I find the status if this was ran for this role, both from a gui and code status?

GRANT EXECUTE TO db_TESTRole

4 
use sqlteam
go

create proc snipeitweeklyinventory_sp
as
/*
	1/28/2021		baby aqua	created
*/
begin
	select 1
end
go

grant execute on snipeitweeklyinventory_sp to [waterworks/babyaqua]

image
image971×743 21.9 KB

1 Like
5

No - you haven't granted execute on anything...the role does not have access to execute everything. You won't get an error because the command does work - it just doesn't grant any permissions.

Issuing that command will do the following:

  1. Create a new user in the database - disabled
  2. Create a new schema in the database owned by the new user

It then looks for any permissions to be granted and since there are none - it exits. It the user already exists in the database - nothing done, so it exits.

6

Jeff, just for clarification, are you referring to this command in your previous reply? I am confused because how can the above create a new user and schema.

7

The first thing GRANT does is checks for a user in the database - if one doesn't exist it creates the user (and the schema). It then grants that user the permissions specified.

So - you wanted to know why the command completed successfully. It is because it checked for the user and either found the user or created the user (and schema). It then checked what permissions to be granted - none were specified so the command exits successfully.

Since you did not grant any permissions - no permissions were added. If the user did not previously exist in that database - the user account was created and disabled. If the user account already existed - then no changes were made (again, you did not specify any permissions to be granted).

And before you ask - this occurs because there are situations where a user is part of a domain group and will be setup with different permissions than the group. For that situation - the user is created in the database and disabled - and grant/deny permissions added to that user so that user's permission is different than the groups.

1 Like