Link to home
Start Free TrialLog in
Avatar of anushahanna
anushahannaFlag for United States of America

asked on

deny all objects but a few, in small code

without 'deny'ing the whole DB one line at a time, is there a command to deny the whole DB objects and all, but except a handful, to a user?

thanks
SOLUTION
Avatar of Lara F
Lara F
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anushahanna

ASKER

by default, all users are part of public role.

let's say there are some objects that have already been assigned to public role - select or executes.

so the new user is going to get the selects and executes also.

So to avoid it- hence the thought to block them from all objects, even if they have been assigned to public, and then start from scratch.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
vdr1620, the goal is before, being able to add new roles, we need to deny to all objects with public permissions, at a minimum, so we can really be sure that the user has access to only the roles to be added.
Have you thought about removing PUBLIC from having access to any of the objects?  
Or are you saying that, now that you have the database all designed and created and everything is working, you want to apply that thought after the fact, i.e. Public has access to everything and now you want to remove it?
Also, which version of SQL Server?
>>that thought after the fact, i.e. Public has access to everything and now you want to remove it?

exactly!

2005.
Well, the Good News is that you are on SS2005.
Okay, how about this?
  1. Script out the permissions you want to grant to the GuestUser.
  2. Above that, script out the steps for dropping the GuestUser and then for creating the Guest User, in that order.
What that should accomplish is the removal of the GuestUser (and, consequently, all of the Guest User's permissions on everything) and then the recreation of the Guest User and subsequently the assignment of the permissions you want that user to have.
>>then for creating the Guest User, in that order.

the new user is going to be borm with permissions to all 'public' objects, not?

in the below code, I would wish the new user will be denied to *all* public objects, which may be many...

create table testtable1 (testcol1 smallint)
insert into testtable1 select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7
create table testtable2 (testcol2 smallint)
insert into testtable2 select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7

CREATE LOGIN testlogin WITH PASSWORD=N'testpw'
CREATE USER testuser FOR LOGIN testlogin

grant select on testtable1 to public
grant select on testtable2 to testuser

select count(*) from testtable1
select count(*) from testtable2
EXECUTE AS USER = 'testuser';
select count(*) from testtable1
select count(*) from testtable2
REVERT;

revoke select on testtable1 to public
revoke select on testtable2 to testuser
EXECUTE AS USER = 'testuser';
select count(*) from testtable1
select count(*) from testtable2
REVERT;
drop user testuser
drop login testlogin
drop table testtable1
drop table testtable2

Open in new window

I'm not sure what your exercies is meant to prove.  You don't show any results of the queries and, quite frankly, I don't have time to copy the code and run it on a database just to find out what you have already found out. :-/
I was initially thinking of the GuestUser . . . sorry about that.
Essentially, you need to revoke/deny privileges on everything to the public role/user.  So what you need to do is to create a stored proc or an SSIS package that will insert the results of the execution each of the following queries into a table.  Once that is done, then you need to execute the SQL statement from each of the rows in that table.
The queries will build the Deny and Revoke statements needed to deny/revoke the privileges that the pulic user/role has.
You might be able to do it wiht SQL_3.

SQL_1:

SELECT 'DENY SELECT ON [sys].' + object_name(major_id) + ' to [public]'
FROM   sys.database_permissions
WHERE  grantee_principal_id=database_principal_id('public');

SQL_2:

SELECT 'revoke all on ' + QUOTENAME(name) + ' from [public]'
FROM   sysobjects
WHERE  objectproperty(id, 'IsMSShipped')=1;

SQL_3:

DENY ALL TO public;

Open in new window

in the example in 33599329, i just showed that give public role to an objects gives permissions to any new user, and we want to avoid it for one new user.

thanks
8080_Diver, i think i mis-communicated.

if we do DENY ALL TO public then we essentially will block out all users who are supposed to have permissions to the public objects. the task at hand is to only block out the new user from accessing any of the public objects.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
wow.. that was really neat and nice way to do it. Thanks so much, guru.
Sometimes, instead of trying to figure out how to unlock the front door, all one has to do is go around back and check the back door . . . because it is probably already unlocked. ;-)
The solution may not directly address your need but it resolves the problem, eh? ;-)
My pleasure . . . it was a fun puzzle. ;-)
your attitude is to be prized and contagious.. keep it up...