Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View. But no matter what I try when I login as this new user I can select from any table. I ran this query:

select  princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
WHERE princ.name = 'public'
AND object_name(perm.major_id) = 'User_Table'

And get these results:

name    type_desc       permission_name     state_desc  class_desc          (No column name)
public  DATABASE_ROLE   DELETE              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   INSERT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   REFERENCES          GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   SELECT              GRANT       OBJECT_OR_COLUMN    User_Table
public  DATABASE_ROLE   UPDATE              GRANT       OBJECT_OR_COLUMN    User_Table

Does this mean no matter what anyone that logs in into the database will have full access based on the 'public' permissions?

share|improve this question
    
Its a Table level of denied permission. Is there any scripts of particular Database level denied acess. –  user25332 Jun 25 '13 at 10:32
add comment

1 Answer

up vote 5 down vote accepted

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
share|improve this answer
    
Is there a way to deny everything to a user in this situation (without doing it table at a time?) I've tried DENY VIEW ANY DATABASE TO New_UserID without luck. –  ProfessionalAmateur Dec 31 '12 at 17:57
    
Define "everything". What exactly do you want the user to be able to do/not do? –  Thomas Stringer Dec 31 '12 at 17:59
    
Our server is hosting an ERP database that runs the entire company from Financials to Human Resources. When you install the system by default the 'public' roles is granted Ins, Sel, Upd, Del access to every table in the entire system. There are thousands of tables in multiple databases on the server. What Im trying to do is create 1 user ID with access to 4 tables and 1 view only. But it seems like the explicit 'public' role access is making that difficult with out manually denying access for this 1 user to all the other tables.... –  ProfessionalAmateur Dec 31 '12 at 18:17
1  
Off the top of my head, I can't think of an easy way around that one. The real question is why does the public role have those excessive permissions??? That's a huge security issue in my opinion. I'd contact the vendor and demand an explanation on that one. Perhaps they will give you written consent to change permissions to the public role if they get push back. I'd be surprised if they actually had a good reason for giving public those permissions. –  Thomas Stringer Dec 31 '12 at 18:38
    
I'm with you, that kind of access is insane. Auditors will have a field day if they find that. No idea why vendor does that. I wonder if I can grant db_denydatareader and db_dendatawriter roles to the user, and then grant back only select to the 4 tables and view? –  ProfessionalAmateur Dec 31 '12 at 18:47
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.