How to tie table to schema?

In article , esquel RemoveThis @sommarskog.se
says...
>
> mat (mat@notarealdotcom.adr) writes:
> > I use the ssms gui for table creation most of the time; ie, I right
> > click the tables entry, select "new table", and add the columns in
> > design view. When I save the table, the simple dialog asks for the table
> > name; it offers nothing re schema, and the table is linked to the dbo
> > schema. I can easily script the new table so that it is transfered to a
> > different schema; but isn't there a simpler way, where I can skip the
> > tranfer? I could create the table via script of course; tsql makes it
> > easy to link the table to a schema.
> >
> > For completeness, is there any way to tranfer a table to a different
> > schema without resorting to tsql?
>
> You are going to hate me, but stop using the table designer, but learn
> the syntax for CREATE TABLE. The table designer has a lot of shortcomings,
> and not being able to select a different schema than default is one of them.
>
> There is certainly a learning curve, but in the long run scripts pay off.
> They can be automated, while point and clicks cannot.

Hate you??? That'd be like hating Robin Hood. You're a major asset!

I know your advice is good; I'm not anti-scripting, many times it's the
best. But right now I do find design view to be useful too. I do lots of
coding with software other than sql server, and having a nice gui like
ssms helps me get things done.
 >> Stay informed about: How to tie table to schema? 

mat (mat@notarealdotcom.adr) writes:
> I know your advice is good; I'm not anti-scripting, many times it's the
> best. But right now I do find design view to be useful too. I do lots of
> coding with software other than sql server, and having a nice gui like
> ssms helps me get things done.

As long as they do it right. And the Table Designer does not always do
that. Be very careful with making changes to tables, there are some
horrible bugs in that area.

Anyway, it occurred to me that there is a way to achieve what you want,
as long as you want all tables in the same schema.

In your database run this:

create login newuser with password = 'P@$$woerd'
create user newuser with default_schema = yourschema
grant control to newuser

(With the names and passwords of your choice of course.) Then connect
Object with this SQL Login. New tables will end up in the default
schema. You may get warnings that you are not the table owner, but you
can ignore these. Or make newuser the owner of the schema.

If you want newuser to have more power on the server, that is OK, but
do *not* add it to the sysadmin role, because then it will map to dbo
in the database. But you can grant it CONTROL SERVER.

--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: How to tie table to schema?