Block user objects from being created in a master database

Question

Post reply

Block user objects from being created in a master database

Steve Rezhener

SSChasing Mays

Points: 615
More actions
November 10, 2020 at 9:09 pm

#3804681

Comments posted to this topic are about the item Block user objects from being created in a master database

Viewing 10 posts - 1 through 9 (of 9 total)

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

BassmanDBA Valued Member Points: 73 More actions · Answer 1

BassmanDBA

Valued Member

Points: 73

November 10, 2020 at 1:15 pm

#3806327

Great stuff man! Thank you for sharing.

SQLOzzie Default port Points: 1442 More actions · Answer 2

What is the syntax to drop the trigger,

DROP TRIGGER trgBlockMasterUserObjects

Since the above does not work

Doug

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

To drop the trigger you need to use the "ON DATABASE" or "ON ALL SERVER" depending if its a database or server scoped DDL trigger

USE [master]

GO

DROP TRIGGER trgBlockMasterUserObjects ON DATABASE

GO

SQLOzzie Default port Points: 1442 More actions · Answer 4

That worked DATABASE needs to be DATABASE not master.

Doug

This reply was modified 4 years, 1 month ago by SQLOzzie.

Steve Rezhener SSChasing Mays Points: 615 More actions · Answer 5

Steve Rezhener

SSChasing Mays

Points: 615

November 10, 2020 at 9:12 pm

#3806563

Sorry. corrected now. thanks for the feedback.

Ron Hinds SSC-Addicted Points: 413 More actions · Answer 6

How do I determine if there are already objects accidentally in master?

Steve Rezhener SSChasing Mays Points: 615 More actions · Answer 7

here is the code for that:

select smas.name, objs.name, *

--, left(smas.name,3)

from master.sys.objects as objs

inner join master.sys.schemas as smas on objs.schema_id = objs.schema_id

where 1=1

--and smas.name NOT IN ('sys', 'INFORMATION_SCHEMA')

and smas.name = 'dbo'

and left(objs.name,3) NOT IN ('sys', 'spt')

and left(objs.name,5) NOT IN ('sp_MS', 'sp_ss', 'MSrep')

and objs.type_desc NOT IN ('INTERNAL_TABLE','SERVICE_QUEUE')

This reply was modified 4 years, 1 month ago by Steve Rezhener.

Ron Hinds SSC-Addicted Points: 413 More actions · Answer 8

Ron Hinds

SSC-Addicted

Points: 413

November 17, 2020 at 4:58 pm

#3808781

Thank you!

SQLOzzie Default port Points: 1442 More actions · Answer 9

This is a little quicker

SELECT OBJECT_SCHEMA_NAME( OBJECT_ID) AS [Schema], Name
FROM sys.All_OBJECTS AS AO
WHERE OBJECT_SCHEMA_NAME( OBJECT_ID) NOT IN ( 'sys', 'INFORMATION_SCHEMA' )
AND OBJECT_SCHEMA_NAME( OBJECT_ID) = 'dbo'
AND LEFT(name,3) NOT IN ('sys', 'spt')
AND LEFT(name,5) NOT IN ('sp_MS', 'sp_ss', 'MSrep')
AND type_desc NOT IN ('INTERNAL_TABLE','SERVICE_QUEUE')
ORDER BY Name;