November 10, 2011 at 12:03 pm
SQL Server automatically creates a fair number of schemas that we arn't using, particularly after an upgrade. As an example there are schemas for each of the built in database roles.
I'm planning to write some code to go through and drop any schema that is not currently in use on ever database on my servers. I'm looking for opinions one way or the other. Does anyone know of any good reason to keep these schemas? Or am I actually going to gain anything by removing them?
Thanks
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
November 10, 2011 at 2:17 pm
I wouldn't recommend removing the system supplied schemas. Since those schemas are owned by the principals associated with them, you would have to modify each principal and define a different schema - then you could remove the schema.
I have removed all of the user created schemas that are not being used. By default, if you create a user and do not change the default schema - it will create that schema. I always assign a default schema(usually dbo, but could another schema) when I create new users.
One thing you should also be aware of is that AD Groups cannot have a default schema created. In that situation, if a user logged in to SQL Server through that AD group and then tries to create a table without schema qualifying the object - SQL Server creates a disabled user and schema in that database for that login.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 11, 2011 at 1:29 am
SQL Server automatically creates a fair number of schemas that we arn't using, particularly after an upgrade. As an example there are schemas for each of the built in database roles.
What’s the harm if you don’t delete them? Ignorance is bliss here.
November 11, 2011 at 2:38 am
herewith the code that I use FYI
/* [drop_schemas.sql]
PURPOSE
drop redundant schemas (i.e. unused, and legacy from way prev SQL behaved)
HISTORY
20101110 dbakercreated
USAGE
sqlcmd -S MLON11U11021A\ULNCSSCN01 -d RMM_GlobalReport -i drop_schemas.sql
*/
-- USE [RMM_GlobalReport]
GO
DECLARE@SchNamesysname
,@sqlvarchar(999)
DECLARE schcur CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
SELECTname
FROMsys.schemasSCH
whereSCH.principal_id not between 1 and 4
andSCH.principal_id not between 16384 and 16399
andnot exists
(select1
fromsys.database_principalsP
whereP.default_schema_name=SCH.name
)
andnot exists
(select1
fromsys.objectsO
whereO.[schema_id]=SCH.[schema_id]
)
order by SCH.name
OPEN schcur
FETCH NEXT FROM schcur into @SchName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql='DROP SCHEMA ['+@SchName+']'
print @sql
exec(@SQL)
FETCH NEXT FROM schcur into @SchName
END
CLOSE schcur
DEALLOCATE schcur
GO
December 2, 2011 at 2:50 pm
Is there any reason not to remove the default schema on any principals where the schema is not in use? With a few exceptions such as dbo and guest. I'm not sure I can see why keeping the db_accessadmin schema is of any use.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
December 3, 2011 at 5:52 am
Is there any reason not to remove the default schema on any principals where the schema is not in use? With a few exceptions such as dbo and guest. I'm not sure I can see why keeping the db_accessadmin schema is of any use.
You will risk backward compatibility features.
Caution
Database schemas introduce other important changes to security from previous versions:
•Code written for earlier releases of SQL Server may return incorrect results if the code assumes that schemas are equivalent to database users.
•Catalog views designed for earlier releases of SQL Server may return incorrect results. This includes sysobjects.
•Ownership chains and user context switching can behave differently now because users can own more than one schema. For more information about ownership chains, see Ownership Chains and Permissions Hierarchy. For more information on context switching, see Context Switching.
•In SQL Server 2000, database objects were owned by users. The four-part reference to a database object in SQL Server 2000 was [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject]. Beginning in SQL Server 2005, the four-part reference to a database object is [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject].
What is the difference between db_accessadmin ROLE and db_accessadmin SCHEMA
User-Schema Separation
http://msdn.microsoft.com/en-us/library/ms190387(v=SQL.90).aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply