changing schemas
in [Server Configuration]
|
From: sqlserver dba on 10 Feb 2010 09:57 We have 3 different schemas in a database and have created user for each schema with default schema set. However for dbas, is there a easy way to switch current schema which is only for that session. In oracle one can do ALTER SESSION SET CURRENT SCHEMA = 'schema_b' What is the equivalent in SQLServer? thanks
From: Tibor Karaszi on 10 Feb 2010 10:40 No such thing in SQL Server, I'm afraid. Default schema is a property of a user, not a session. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "sqlserver dba" <nobody(a)nowhere.com> wrote in message news:hkuhg701nsi(a)drn.newsguy.com... > We have 3 different schemas in a database and have created user for each > schema > with default schema set. However for dbas, is there a easy way to switch > current > schema which is only for that session. In oracle one can do > ALTER SESSION SET CURRENT SCHEMA = 'schema_b' > > What is the equivalent in SQLServer? thanks >
From: Jeffrey Williams on 10 Feb 2010 13:53 What problem are you trying to solve? For any user with permissions, they can access objects in any schema by schema qualifying the object. For example: SELECT {columns} FROM MySchema.MyTable; Instead of: SELECT {columns} FROM MyTable; It is also a recommendation on SQL Server 2005 and above that you schema qualify your objects. This has an effect on the plan cache if you are not doing so. When a query that does not schema qualify the objects is executed by UserA - a plan will be created, when that same query is execute by UserB - another plan is generated in that users schema. Jeff "sqlserver dba" <nobody(a)nowhere.com> wrote in message news:hkuhg701nsi(a)drn.newsguy.com... > We have 3 different schemas in a database and have created user for each > schema > with default schema set. However for dbas, is there a easy way to switch > current > schema which is only for that session. In oracle one can do > ALTER SESSION SET CURRENT SCHEMA = 'schema_b' > > What is the equivalent in SQLServer? thanks >
From: sqlserver dba on 10 Feb 2010 14:01 >What problem are you trying to solve? Nothing except that I as DBA has to support multiple schemas and I need a quick way to park myself temporarily in one schema. Otherwise the application and all stored procedures qualify an object name with schema name also.
From: Jeffrey Williams on 10 Feb 2010 14:12
Not sure I understand what you mean by 'park' yourself in a schema. Can you provide an example of what you mean? "sqlserver dba" <nobody(a)nowhere.com> wrote in message news:hkuvq002m3s(a)drn.newsguy.com... >>What problem are you trying to solve? > > Nothing except that I as DBA has to support multiple > schemas and I need a quick way to park myself temporarily > in one schema. > > Otherwise the application and all stored procedures qualify > an object name with schema name also. > |