Enable 'Allow modifications to be made directly to the system catalogs'

Question

Enable 'Allow modifications to be made directly to the system catalogs'

Viewing 14 posts - 16 through 28 (of 28 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How about posting the objects and permissions that you can't drop. Then we can advise appropriately.

I know you don't want a lecture, but I wasn't joking when I said that it is possible to completely break a database by playing with the system tables. Since this is master, that means a completely broken instance. You don't want to mess with the system tables unless you know them backwards. (and even then maybe not). Do note that they all changed from SQL 2000. Sysusers is not a table any longer, neither are any of the ones that, on SQL 2000, you would just delete from.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 2

falcon59x (1/12/2010)
Hey guys,
Thanks for the response they are really helping. When I incorporated searching for the schema instead of just using sys. I was actually able to knock 26 down to 21. Then as silly as this sounds one of my variables needed to be lengthened in order and that brought it down to 20. However, I am still trying to get ride of those last few! Paul would you at least be willing to point me towards some resources to help me figure out how to enable 'allow modifications to be made directly to the system catalog'?
Thanks,

can you show us the 20 specific REVOKE statements that are not executing?

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!

falcon59x SSC Eights! Points: 804 More actions · Answer 3

I Removed my code, because I didnt feel comfterble having it up and these guys where able to show me what I was doing wrong. It wasnt working anyway :-D!

falcon59x SSC Eights! Points: 804 More actions · Answer 4

These are the ones that wont die Ill run the statment and it will return the error saying that it cannot find the object or I dont have permission. I also noticed that they where all in Capslock? While none of the others where... I dont know what thats supposed to mean.

publicCHECK_CONSTRAINTS SELECT

publicCOLUMN_DOMAIN_USAGE SELECT

publicCOLUMN_PRIVILEGES SELECT

publicCOLUMNS SELECT

publicCONSTRAINT_COLUMN_USAGE SELECT

publicCONSTRAINT_TABLE_USAGE SELECT

publicDOMAIN_CONSTRAINTS SELECT

publicDOMAINS SELECT

publicKEY_COLUMN_USAGE SELECT

publicPARAMETERS SELECT

publicREFERENTIAL_CONSTRAINTS SELECT

publicROUTINE_COLUMNS SELECT

publicROUTINES SELECT

publicSCHEMATA SELECT

publicTABLE_CONSTRAINTS SELECT

publicTABLE_PRIVILEGES SELECT

publicTABLES SELECT

publicVIEW_COLUMN_USAGE SELECT

publicVIEW_TABLE_USAGE SELECT

publicVIEWS SELECT

Lowell SSC Guru Points: 323504 More actions · Answer 5

those all look like objects owned by INFORMATION_SCHEMA

since your cursor is hardcoded to sys.objectname, it is trying to revoke sys.COLUMNS , which does not exist, instead of INFORMATION_SCHEMA.COLUMNS , which does exist.

i identified that issue in the script i provided, did you try it at all?

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Those are views in the INFORMATION_SCHEMA schema. However, afaik they are not in master at all, but are in the hidden system database (which you cannot edit).

Try

REVOKE SELECT ON INFORMATION_SCHEMA.CHECK_CONSTRAINTS to public

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

falcon59x SSC Eights! Points: 804 More actions · Answer 7

Hey Guys,

That worked thank you all sooo much. Lowell Sorry I didn't directly implement the code I tried to use the concepts(I didn't want to just copy it, the better pursuit would have been to ask for help understanding what exactly was happening.) and I did search on the schemas that where not disappearing I could have sworn it returned sys. and so I figured your code was just doing what mine was apparently I was wrong. Thanks for taking the time to write it though or I would may have spent so much time pulling my hair out I woulda been bald! Anyway You guys are awesome and thanks for helping me out!

Thanks,

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Out of curiosity, now that you are able to remove the public role permissions. Do you have a plan in place or substitute role in place should issues arise when this is deployed to prod?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

EdVassie SSC Guru Points: 60445 More actions · Answer 9

If you are trying to lock down security then revoking all access to public in the master database is not the best way to do this. What you have done may or may not be a good idea for other database systems, but is definitely the wrong thing to do in SQL Server. If you are interested in finding the best way to secure SQL Server then please start a new thread asking 'How do I lock down security in SQL Server'.

The changes you have made mean you have taken your SQL Server instance out of Microsoft support. If you find that some things on your system do not work as expected and ask Microsoft for help, then expect them to ask you to reproduce the problem when standard permisions are applied. If the problem does not happen with the Microsoft standard permissions then you know that your work has broken SQL Server.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

falcon59x SSC Eights! Points: 804 More actions · Answer 10

Hey Guys,

-No I dont have a back up plan as of yet. Howerver, this thing will be going through testing before its release and if anything comes up it will be dealt with then luckily this script is for after we install SQL server so if it mucks it up we can just remove it and start again. (Not to mention my personnal tests show it to have all the functionality I need it to have)

-Seems to work fine to me? Its doing everything I need it to do! Plus I wasnt told lock down SQL i was told remove public permissions from all the databases soooo... yea!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

OK Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

vedpsoni Old Hand Points: 369 More actions · Answer 12

IN SQL 2000

1.Right click onto your server node, - an SQL Server Properties dialog will popup, In server setting

2.Enable "Allow modifications to be made directly to the system catalogs" option

Paul White SSC Guru Points: 150467 More actions · Answer 13

vedpsoni (7/17/2010)
IN SQL 2000
1.Right click onto your server node, - an SQL Server Properties dialog will popup, In server setting
2.Enable "Allow modifications to be made directly to the system catalogs" option

This is always a bad idea.

In any case, the solution in this thread did not require any such dangerous manoeuvres - just a REVOKE statement.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

vedpsoni Old Hand Points: 369 More actions · Answer 14

Thanks paul

but can u describe your statement

thanks again