changing schemas
in [Server Configuration]
|
From: sqlserver dba on 10 Feb 2010 19:11 In article <#9rl3ToqKHA.6140(a)TK2MSFTNGP05.phx.gbl>, Jeffrey Williams says... > >Not sure I understand what you mean by 'park' yourself in a schema. Can you >provide an example of what you mean? By park I mean I will be in SSMS for some time researching various SQLs and sprocs and at that time it is a pain for me to fully qualify the table name. In one case I ended up looking at wrong table (owned by dbo) which had the same name like SCHEMA.table. When I know the context of my work is one particular schema, I would prefer to set something which will always refer to that schema first, instead of dbo. > >"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. >>
From: Kalen Delaney on 10 Feb 2010 19:34 Hi If you are in the db_owner role, you can use the SETUSER command to impersonate a user who has the desired default schema. You could set up a 'dummy' user for each schema, and then in place of your ALTER SESSION you would just do SETUSER 'some user name' -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "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 11 Feb 2010 10:20
SETUSER 'username' works as I want it. Thanks. In article <DB8826B9-E327-4E2D-AF5B-4199C63A4513(a)microsoft.com>, Kalen Delaney says... > >Hi > >If you are in the db_owner role, you can use the SETUSER command to >impersonate a user who has the desired default schema. >You could set up a 'dummy' user for each schema, and then in place of your >ALTER SESSION you would just do >SETUSER 'some user name' > >-- >HTH >Kalen >---------------------------------------- >Kalen Delaney >SQL Server MVP >www.SQLServerInternals.com |