Hi,

I'm currently testing SS2005 (the free version, Express I believe) for
work and I am trying to use the default schemas to mimic the
search_path ability of Postgres but it doesn't seem to be working.
For example:

=========================================
alter user my_user with default_schema = dbo
go
create schema my_schema
go
alter user my_user with default_schema = my_schema
go
create table old_users(id int, user_name varchar(255))
go
=========================================

My understanding is that this would create a table called "old_users"
in the "my_schema" schema. However when I look the table that gets
created is "dbo.old_users". I'm obviously missing something but I can
see what.

Thanks in advance for you ! (and sorry if this has been posted
before, I seached through the groups and on Google and haven't found
anything particularly ful yet)

> My understanding is that this would create a table called "old_users"
> in the "my_schema" schema. However when I look the table that gets
> created is "dbo.old_users". I'm obviously missing something but I can
> see what.

What user is executing this script? If you login as 'my_user' or change the
security context with EXECUTE AS, I would expect the table to be created in
the default schema as long has 'my_user has create permissions:

GRANT ALTER ON SCHEMA::my_schema TO my_user;
GO
EXECUTE AS USER = 'my_user';
GO
CREATE TABLE old_users(id int, user_name varchar(255));
GO

--
Hope this s.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

<ajwhite@gmail.com> wrote in message
news:613338f2-03b4-4623-aff9-7a89a35acbec@t54g2000hsg.googlegroups.com...
> Hi,
>
> I'm currently testing SS2005 (the free version, Express I believe) for
> work and I am trying to use the default schemas to mimic the
> search_path ability of Postgres but it doesn't seem to be working.
> For example:
>
> =========================================
> alter user my_user with default_schema = dbo
> go
> create schema my_schema
> go
> alter user my_user with default_schema = my_schema
> go
> create table old_users(id int, user_name varchar(255))
> go
> =========================================
>
> My understanding is that this would create a table called "old_users"
> in the "my_schema" schema. However when I look the table that gets
> created is "dbo.old_users". I'm obviously missing something but I can
> see what.
>
> Thanks in advance for you ! (and sorry if this has been posted
> before, I seached through the groups and on Google and haven't found
> anything particularly ful yet)

On Jun 12, 8:17pm, "Dan Guzman" <guzma...@nospam-
online.sbcglobal.net> wrote:
> > My understanding is that this would create a table called "old_users"
> > in the "my_schema" schema. However when I look the table that gets
> > created is "dbo.old_users". I'm obviously missing something but I can
> > see what.
>
> What user is executing this script? If you login as 'my_user' or changethe
> security context with EXECUTE AS, I would expect the table to be created in
> the default schema as long has 'my_user has create permissions:
>
> GRANT ALTER ON SCHEMA::my_schema TO my_user;
> GO
> EXECUTE AS USER = 'my_user';
> GO
> CREATE TABLE old_users(id int, user_name varchar(255));
> GO
>
> --
> Hope this s.
>
> Dan Guzman
> SQL Server MVPhttp://weblogs.sqlteam.com/dang/
>
> <ajwh...@gmail.com> wrote in message
>
> news:613338f2-03b4-4623-aff9-7a89a35acbec@t54g2000hsg.googlegroups.com...
>
>
>
> > Hi,
>
> > I'm currently testing SS2005 (the free version, Express I believe) for
> > work and I am trying to use the default schemas to mimic the
> > search_path ability of Postgres but it doesn't seem to be working.
> > For example:
>
> > =========================================
> > alter user my_user with default_schema = dbo
> > go
> > create schema my_schema
> > go
> > alter user my_user with default_schema = my_schema
> > go
> > create table old_users(id int, user_name varchar(255))
> > go
> > =========================================
>
> > My understanding is that this would create a table called "old_users"
> > in the "my_schema" schema. However when I look the table that gets
> > created is "dbo.old_users". I'm obviously missing something but I can
> > see what.
>
> > Thanks in advance for you ! (and sorry if this has been posted
> > before, I seached through the groups and on Google and haven't found
> > anything particularly ful yet)- Hide quoted text -
>
> - Show quoted text -

Sweet, that worked! I should probably do some reading to understand
which (if not both) part b/t the grant and execute fixed my issues but
for now I'm just glad to get things working!


Thanks,

Andrew