2 added 1387 characters in body
source | link

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied. In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Example

use TestDB;
go

create login Login1
with
    password = 'password',
    check_policy = off;
go

create user User1
for login Login1;
go

-- this will return 1 (meaning, yes it is a role member)
select is_rolemember('public', 'User1');


-- let's do a test to show the above theory
create table SomeTable
(
    id int identity(1, 1) not null,
    SomeText varchar(30) not null
        default replicate('a', 30)
);
go

insert into SomeTable
values(default);
go 10

-- give the public role permissions to SELECT on SomeTable
grant select
on SomeTable
to public;
go

-- this will be successful, because User1 is part of public
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go


-- create a new role to deny SELECT on SomeTable
exec sp_addrole 'Role1';
go

deny select
on SomeTable
to Role1;
go

-- add User1 to this new role
exec sp_addrolemember 'Role1', 'User1';
go


-- this will not be successful because User1 now has been denied SELECT on SomeTable
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied. In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied. In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Example

use TestDB;
go

create login Login1
with
    password = 'password',
    check_policy = off;
go

create user User1
for login Login1;
go

-- this will return 1 (meaning, yes it is a role member)
select is_rolemember('public', 'User1');


-- let's do a test to show the above theory
create table SomeTable
(
    id int identity(1, 1) not null,
    SomeText varchar(30) not null
        default replicate('a', 30)
);
go

insert into SomeTable
values(default);
go 10

-- give the public role permissions to SELECT on SomeTable
grant select
on SomeTable
to public;
go

-- this will be successful, because User1 is part of public
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go


-- create a new role to deny SELECT on SomeTable
exec sp_addrole 'Role1';
go

deny select
on SomeTable
to Role1;
go

-- add User1 to this new role
exec sp_addrolemember 'Role1', 'User1';
go


-- this will not be successful because User1 now has been denied SELECT on SomeTable
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go
1
source | link

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied. In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

public Database Role
Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.