script to run

Question

Post reply

script to run

Tara-1044200

SSCoach

Points: 15951
More actions
February 23, 2011 at 2:47 pm

#143415

I have a script which would deny permissions to some group of people on certain tables. I would like to run this script on a daily basis if there are any new databases created on that day, how would i do this effecently.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 1

Just use a scheduled sqlagent job that runs your script for every userdb.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Lowell SSC Guru Points: 323518 More actions · Answer 2

I've done what you are asking with a server wide DDL trigger that looks for the CREATE_DATABASE event;

basically, my example is whenever a database is created, a suite of specific roles get added.

this is a trimmed down, but working example. see how i had to use dynamic sql to do the right database context? i couldn't think of a different way to do it.

I don't know if a RESTORE to a new database raises the CREATE_DATABASE event, but i suspect it does.

so based on this example, if you can add a role (and users...that is part of my real script) you could add DENY and REVOKE commands and all that as well.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [SERVER_TR_ADD_ROLES_TO_NEW_DBS]

ON ALL SERVER

FOR CREATE_DATABASE

AS

--permissions issues:

EXECUTE AS LOGIN = 'superman'

declare @eventData XML;

declare @dbname varchar(100);

set @eventData = EVENTDATA()

SELECT @dbname = @eventData.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME')

--Dynamic SQL so we can get the context?

DECLARE @sql VARCHAR(max)

PRINT 'dbname ' + ISNULL(@dbname,'whoops!')

SET @sql='

USE [THEDBNAME];

--USER_TABLE: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

PRINT db_name() + '': ADDING ROLE_ALLTABLES_FULL ''

IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''ROLE_ALLTABLES_FULL'' AND type = ''R'')

CREATE ROLE ROLE_ALLTABLES_FULL

'

SET @sql = REPLACE(@sql,'[THEDBNAME]',@dbname)

PRINT '@sql' + ISNULL(@sql,' double whoops!')

EXECUTE(@sql)

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [SERVER_TR_ADD_ROLES_TO_NEW_DBS] ON ALL SERVER

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

I just checked, and if you restore from a backup, and change the database name to a new database, it does not trigger the create_database event...so my trigger example wouldn't fire.

I think a scheduled job that searches for whether the deny/revoke permissions exist or not , and executes them is the only way to go.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 4

Lowell (2/24/2011)
I just checked, and if you restore from a backup, and change the database name to a new database, it does not trigger the create_database event...so my trigger example wouldn't fire.
I think a scheduled job that searches for whether the deny/revoke permissions exist or not , and executes them is the only way to go.

Just as a side note:

- If the restored database had the revoke statements already run, they would of course still be in place after the restore.

- on top of your ddl event, you could also install a AUDIT_BACKUP_RESTORE_EVENT SSB app that can take care of your issue after the restore completes.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

In my case tables are not created when the database is created so i have to check if there is any database created every day and ru nthe script.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 6

How can i script out database names to check if deny permission ofr id's X and Y on tables A,B,c are existing if not run the script i have on that particular database.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 7

is there any script or best way to find the tables and check if there are any deny permissions if not i want the script to run that database which deny permissions to certain users.

or

can i give way access to a non sys admin/non security admin to run the script which will deny permissions on those tables.