Re: How to script security changes for all tables but a few



I got a script to work...
What I did was first to create two groups for the two schemas.
so schema test1 and schema test two with groups called db_denaytest1
db_denytest2
I made the sql user a member of db_denytest1 which is denied access to
test1 schema. However the user needs access to db_denytest2 schema,
so the user could not be a member of that group.

Then I run this script and then further restrict access to the two
table's the user needs access to.

/*
************************Read this note before running the
script***********************
Use the following commands to deny schema's to a user if your DB has
multiple Schemas
You will need to remove the comment '--' remarks to run deny schema
It should be noted that if you deny a schema to a user or group that
any member of the group
will can not access a table in that schema even if explicitly granted

For production DB there are two groups 'DenyPRODCTL' & 'DenyPRODDTA'
Add the user
account to the schem they do not need access to. Then goto the Script
permissions secctions
and make your updates accordingly.

***Be sure you read all comments written before running scripts***
Be sure you are connected to correct database, and or change the USE
DB to the correct database
*/

use ReplaceValueWithDBName
go


-- Here is samples on how to deny access to schema to a user or
group... be sure to test in a non production env.
--Deny select on SCHEMA::ChangemeSchemaName TO ChangeMeUserName,Or
Groupname
--deny ALTER on SCHEMA::SchemaName TO UserName,Or Groupname
--deny UPDATE on SCHEMA::SchemaName TO UserName,Or Groupname
--deny INSERT on SCHEMA::SchemaName TO UserName,Or Groupname
--deny DELETE on SCHEMA::SchemaName TO UserName,Or Groupname


--Start of script permissions changes
declare @count as numeric;
declare @tabschema_nm as varchar(300);
set @count = 0


declare c1 cursor for

select table_schema +'.'+ table_name as fulltable
from information_schema.tables
-- Be sure to set the Table_Schema value to the schema you want to
change permissions for
where table_type ='Base Table' and table_schema='test1' --set schema
here that you want to affect

/*Change the list of tables not in that you do not want to change the
permissions for
Once done you will need to manually set the permissions on the tables
to control how that user or group
is allowed to interact with those tables
*/

and TABLE_SCHEMA+'.'+TABLE_NAME NOT IN ('test1.F390','test1.F981'') --
Change your table info

--End of table select script

--Start of script for tables select permission changes

OPEN C1;
FETCH NEXT FROM C1 INTO @tabschema_nm ;
WHILE (@@FETCH_STATUS = 0)
BEGIN

set @count = @count + 1

/*
Ensure you replace the name in brackets to the correvct the SQL user,
or group.
*/

EXEC ('DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON '
+ @tabschema_nm + ' TO [SETUSERNAMEHERE]')

fetch next from c1
into @tabschema_nm
end
print 'Tables processed ' + str(@count)


CLOSE C1;
DEALLOCATE C1;

.