script on all databases

Question

script on all databases

Tara-1044200

SSCoach

Points: 15951
More actions
September 18, 2010 at 9:28 am

#145191

how can i exec this trigger in all db's of the server ?
alter TRIGGER [Trg_DenyPerm]
ON DATABASE
FOR
CREATE_TABLE
AS
BEGIN
SET NOCOUNT ON
DECLARE
@eventData XML,
@DBName SYSNAME,
@EVENTDATE DATETIME,
@USERNAME SYSNAME,
@SYSTEMUSER VARCHAR(128),
@CURRENTUSER VARCHAR(128),
@ORIGINALUSER VARCHAR(128),
@HOSTNAME VARCHAR(128),
@APPLICATIONNAME VARCHAR(128),
@SCHEMANAME SYSNAME,
@OBJECTNAME SYSNAME,
@OBJECTTYPE SYSNAME,
@EVENTTYPE VARCHAR(128),
@COMMANDTEXT VARCHAR(max),
@NAMEFORDEFINITION VARCHAR(261)
SET @eventData = eventdata()
SELECT
@DBName = db_name(),
@EVENTDATE = GETDATE(),
@USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
@SYSTEMUSER = SUSER_SNAME(),
@CURRENTUSER = CURRENT_USER,
@ORIGINALUSER = ORIGINAL_LOGIN(),
@HOSTNAME = HOST_NAME(),
@APPLICATIONNAME = APP_NAME(),
@SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
@OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
@OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
@COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
@EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
IF @OBJECTTYPE ='TABLE'
BEGIN
IF @OBJECTNAME in ('EligDates')
BEGIN
IF SUSER_SNAME() NOT IN('sa','sysadmin','state\MLevan')
BEGIN
RAISERROR('You do not have to this table', 16, 1)
ROLLBACK
END
ELSE
BEGIN
EXEC master.dbo.DenyPerm @DBName
END
END
END
END

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Are you familiar with the SP (In master DB) sp_FOREACHDB? To read how to use this go to:

http://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

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

I have alreayd tried it but i think it will not work as i try it says " TRIGGER should be the first statement"

shahm10 SSCrazy Points: 2217 More actions · Answer 3

This is not the most efficient way of doing it but it works

SELECT ' USE ' + Name +

char (10) + ' go ' + char(10)

+ ' alter statement ' + char (10) + ' go'

from sys.databases

output results to text

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

You can make use of sqlcmd and provide a input file(with sql script) to it and run it on desired databases.

MJ

Jeff Moden SSC Guru Points: 1004299 More actions · Answer 5

You collect a bunch of data and never use it. I'm just curious as to why not.

Also, it's a form of "Death by SQL" to hardcode a user name anywhere in the database. Spend some time looking up privs and roles instead of using a trigger to attempt to overcome the problem. It's just going to get worse down the line if you use that trigger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)