InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 06:00:40 GMT20http://www.sqlservercentral.com/Forums/Topic477499-146-1.aspxHI chandru,I posted one topic ,could you please help me Fri, 11 Apr 2008 06:19:51 GMTsps-554505http://www.sqlservercentral.com/Forums/Topic477499-146-1.aspxThanks alot,Also I would need couple of details ,could you help me---This is what I needed,1)Objects which do not belong to DBOThis is the query we are using to find outselect schema_name(schema_id)as ObjectName, name,type,type_desc from sys.objectswhere schema_name(schema_id) <> 'dbo'and type in('U','P','V', 'FN','TF','IF','PK','UQ')go2)Database users which do not have DBO as their default schemaSELECT *FROM sys.database_principalsWHERE [type] IN ('U','S') AND ISNULL(default_schema_name,'')<>'dbo'We would like to create a report out of it and let a procedure run in every instance on a daily basis. The procedures should write the information into seperate tables in theOne of DB(Like ABC)databaseI need to have the Sp in one Database and it needs to check all the Database and fetch the details into separate tables as per the database nameCould you please help meFri, 11 Apr 2008 06:17:44 GMTsps-554505http://www.sqlservercentral.com/Forums/Topic477499-146-1.aspxYou can use the following script for a specific database -SELECT *FROM sys.database_principalsWHERE [type] IN ('U','S') AND ISNULL(default_schema_name,'')<>'dbo'Fri, 11 Apr 2008 02:36:16 GMTChandrachurh Ghoshhttp://www.sqlservercentral.com/Forums/Topic477499-146-1.aspxhi,Is there any method to find out Database users which do not have DBO as their default schema.Any querySmmFri, 11 Apr 2008 02:11:56 GMTsps-554505http://www.sqlservercentral.com/Forums/Topic477499-146-1.aspx[quote][b]shine.mm (4/2/2008)[/b][hr]Hi,What i would need is i would like to know each databases,Which are the Objects(Tables,Stored Procedures Etc.)not belong to the schema DBO.Fist i need to know which are the objects not belong to DBOThen i would like to change the schema of this objects to DBOPlease help me Smm[/quote]This query will give you objects in a single database that do not belong to dbo. I've also constructed the command you'd need to run to transfer the object to a different schema.[code]select schema_name(schema_id), name, command = 'ALTER SCHEMA dbo TRANSFER [' + schema_name(schema_id) + '].[' + name + ']'from sys.objectswhere schema_name(schema_id) <> 'dbo'and type in('U','P','V', 'FN','TF','IF')[/code]Thu, 03 Apr 2008 00:48:37 GMTSQLZhttp://www.sqlservercentral.com/Forums/Topic477499-146-1.aspxHi,What i would need is i would like to know each databases,Which are the Objects(Tables,Stored Procedures Etc.)not belong to the schema DBO.Fist i need to know which are the objects not belong to DBOThen i would like to change the schema of this objects to DBOPlease help me SmmWed, 02 Apr 2008 23:50:48 GMTsps-554505http://www.sqlservercentral.com/Forums/Topic477499-146-1.aspx[quote][b]shine.mm (4/1/2008)[/b][hr]HI All,I would like to change the default schema details of all the logins createdCould yoy please specify how do i find out the schema detailsand also i would like to change all the objects default schema to DBORegards'Smm[/quote]What schema details do you want to know exactly?To change the default schema:[code]ALTER USER Mary51 WITH DEFAULT_SCHEMA = dbo[/code]To change objects to the default schema use:[code]ALTER SCHEMA dbo TRANSFER fully_qualified_object_name[/code]Hope that helps.Tue, 01 Apr 2008 05:12:18 GMTSQLZhttp://www.sqlservercentral.com/Forums/Topic477499-146-1.aspxHI All,I would like to change the default schema details of all the logins createdCould yoy please specify how do i find out the schema detailsand also i would like to change all the objects default schema to DBORegards'SmmTue, 01 Apr 2008 04:50:07 GMTsps-554505