model database

Question

Post reply

model database

Tara-1044200

SSCoach

Points: 15951
More actions
September 8, 2010 at 11:39 am

#141961

Every time i create a database on the server there would be 3 tables created which would be standard across the server,once these tables are created i revoke alter permissions on these table for some users. how would i automate this ?
can i do somethign in the model database where that would apply across all the databases when created?

Viewing 11 posts - 1 through 11 (of 11 total)

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

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 1

Hi Tara,

Just do that in Model DB and those changes get reflected in al the new dbs created inclusing those permissions.

Thank You,

Best Regards,

SQLBuddy.

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

I have a stored proc to do that but how does it get executed as and when there is a new database?

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 3

Stored procedure to do what ..

Thank You,

Best Regards,

SQLBuddy

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

The SP i created will deny permission for some users on few tables to drop or update them. I would like this to be executed as and when new database is created. The new database will have these tables by default when created.

crazy4sql SSCoach Points: 19590 More actions · Answer 5

you need to create trigger for this event and place the trigger in master. It will keep track of any new databases addedd and will execute accordingly.

----------
Ashish

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

how does the trigger keep track of any new database?

crazy4sql SSCoach Points: 19590 More actions · Answer 7

it will fired whenever there is any syntax like create database either by query analyzer or EM

----------
Ashish

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

If those tables are fixed and if those users are fixed with their permissions do that table creation and assign those required permisiions to those users in the Model DB and that should be replicated for evry new db that will be created.

Thank You,

Best Regards,

SQLBuddy

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

tables are fixed but will be created after the database is created.

If i have to execute this through a trigger how do i fire it across all databases when these tables are created.

crazy4sql SSCoach Points: 19590 More actions · Answer 10

if your tables are fixed then just add those tables in model. And everytime you create new database, these tables will be there. As well you can configure the users permission those table which will also replicated similarly.

----------
Ashish