Deny Public Role Permissions
-
I just completed a clean install of SQL 2005 and run the 2005 Best Practices Analyzer. It makes little sense to me why the Public Role default permissions are all granted to the 6 databases during installation, and then the BPA immediately recommends that they should be turned off.
I looked long and hard online to find a way to disable all of the permissions and found very little. I am fairly new to SQL administration, so if anyone has any recommendations for places to go for good documentation, I'm all ears.
After a lot of trial and error, my coworker and I developed these queries. Hopefully they will be of use to someone else in the future.
This query will produce a list to remove all public permissions to objects belonging to a particular schema. (Run this query, then paste the results into a new query.) The [sys] tag will work for most databases, but check in the "Securables" tab in the database properties for other schemas.
SELECT 'DENY SELECT ON [sys].' + object_name(major_id) + ' to [public]'
from sys.database_permissions where grantee_principal_id=database_principal_id('public')
There is another script posted here that will return a list of public exec permissions that were shipped as part of the Microsoft installation. I didn't have much luck with that, but this query will return a list of objects with public exec permissions that shipped. Pasting the results of this query into a new one will remove permissions to these objects.
select 'revoke all on ' + QUOTENAME(name) + ' from [public]'
from sysobjects where objectproperty(id,'IsMSShipped')=1
go
This is the first query we wrote. I'm not completely sure if it does anything that much different than the other two, but here it is anyway, just in case it is helpful.
select 'revoke all on ' + QUOTENAME(name) + ' from [public]'
from sysobjects where objectproperty(id,'IsMSShipped')=1
go
Here's the remaining problem:
I have successfully managed to remove all Public permissions to 5 of the 6 databases that were installed with SQL 2005. However, there are still 4 objects in my Master database that I can't get rid of. The major_id comes back as a 9 digit, negative number. Google has been no help identifying the numbers. The object name function, however, returns a "NULL" value on all four of these. It's as if these objects exist, but don't.
Does anyone have any suggestions on how to find out exactly what these objects are, and how to remove Public Role permissions to them?
-
Is the major_id same for everytime you run the query. If not it might be any dynamic objects that sql server creates on startup.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
I almost forgot that I had posted this! After a lot of digging around, we determined that these objects either don't really exist, or shouldn't exist.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply