Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Enable 'Allow modifications to be made directly to the system catalogs' Expand / Collapse
Author
Message
Posted Monday, January 11, 2010 11:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 2, 2010 11:19 AM
Points: 18, Visits: 33
Hey,

My last question didnt get much help so I thought I would ask a more direct one with less parts. How do I enable 'Allow modifications to be made directly to the system catalogs' with SQL?

Thanks
Post #845604
Posted Monday, January 11, 2010 11:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
since 2005, it's no longer possible. the old SQL 2000 syntax is ignored.

what is it you think you want to change, that cannot be done with the normal commands?


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #845607
Posted Monday, January 11, 2010 12:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 2, 2010 11:19 AM
Points: 18, Visits: 33
First thanks for the reply,

Secondly I am trying to delete access to Object Permissions for the public user. (I am not worried about the side affects, it has to be done) The SQL comman I am using deletes all of the Object Permissions for public accept 26 Now I have read that if I dont enable the Allow modifications to be made directly to the system catalogs' I cant delete them all and I am assuming this is the problem which I am facing. Is there a work around in 2005 or Am I just screwed?
Post #845624
Posted Monday, January 11, 2010 12:17 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 2, 2010 11:19 AM
Points: 18, Visits: 33
Thats 26 is the number of Object Permissions sorry I should have clarrified that!
Post #845630
Posted Monday, January 11, 2010 12:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
lets look at the details....show us the SQL you are using to identify objects you want to take away access from; that will really let us help you better.

if it is things like views for sysobjects or something like that, that would be an example of a change you cannot make.

from BOL:

http://msdn.microsoft.com/en-us/library/bb669065.aspx
The public Role
The public role is contained in every database, which includes system databases. It cannot be dropped and you cannot add or remove users from it. Permissions granted to the public role are inherited by all other users and roles because they belong to the public role by default. Grant public only the permissions you want all users to have.



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #845633
Posted Monday, January 11, 2010 12:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 2, 2010 11:19 AM
Points: 18, Visits: 33
USE [database name]
SELECT u.name 'User', o.name 'Object', p.permission_name 'Action'
FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.grantee_principal_id IN (0, 2)
ORDER BY u.name, o.name, p.permission_name

It is displaying a list of SELECT and EXECUTE.I can delete all except the last 26 which are a mix of the two.
Post #845638
Posted Monday, January 11, 2010 12:49 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 42,774, Visits: 35,872
Use the REVOKE statement to remove permissions.

Example
REVOKE SELECT on SomeTable TO Public

Specific statement depends on what the objects are.

Directly editing the system tables, even on SQL 2000, was just asking for a corrupt database. I had to clean up often enough after people who just wanted to quickly edit the system tables and not care about the side effects (which can be a completely unusable database if you're not careful)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #845652
Posted Monday, January 11, 2010 12:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 3:37 PM
Points: 4,389, Visits: 9,522
What is the purpose of removing that access from the public role? What is it you are trying to accomplish here?

Take a look at VIEW DEFINITION in books online. I think that might be what you are trying to get at.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #845658
Posted Monday, January 11, 2010 1:39 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 2, 2010 11:19 AM
Points: 18, Visits: 33
- I already have the Revoke applied my code uses a cursor to run through each Object name. Unfortuantly though there are a few that its not sufficent to delete. I appriciate your concern, but I implied i dont care about the side effects to avoid a lecture. Plus I am Installing the SQL and running this script to set it up. No unfourtnate DBA's or SA's will get stuck cleaning it up.

-I have to delete these permissions from the user, beause its a secure system. So we cannot allow anyone assigned to Public to have any access.

Post #845687
Posted Monday, January 11, 2010 3:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:39 AM
Points: 12,906, Visits: 31,985
ok i think i'm following along; i created a brand new database in SQL 2005, and ran the following script to generate the revoke statements; i think the difference for me is i'm specifically getting the schema name of the objects as well, and that is allowing me to drop access to things like sys.all_columns:
--CREATE DATABASE [SandBox]
GO
USE [SandBox]
SELECT
'REVOKE ' + convert(varchar(50),x.[Action])
+ ' on ' + x.[Schema]
+ '.' + convert(varchar(50),x.[Object])
+ ' TO ' + convert(varchar(50),x.[User]) COLLATE Latin1_General_CI_AS
FROM (
SELECT
u.name COLLATE Latin1_General_CI_AS AS 'User',
schema_name(o.schema_id) As 'Schema',
o.name COLLATE Latin1_General_CI_AS AS 'Object' ,
p.permission_name COLLATE Latin1_General_CI_AS AS 'Action'
--into tmp
FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o
WHERE o.object_id = p.major_id
AND p.grantee_principal_id = u.principal_id
AND p.grantee_principal_id IN (0, 2)
) X





Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #845742
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse