How to tie table to schema?
in [Server Configuration]
|
From: mat on 8 Jul 2010 03:19 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?
From: Erland Sommarskog on 8 Jul 2010 06:46 mat (mat(a)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. -- Erland Sommarskog, SQL Server MVP, esquel(a)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
From: mat on 8 Jul 2010 12:36 In article <Xns9DAF81EBF5487Yazorman(a)127.0.0.1>, esquel(a)sommarskog.se says... > > mat (mat(a)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.
From: Erland Sommarskog on 8 Jul 2010 16:45 mat (mat(a)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(a)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
|
Pages: 1 Prev: Event ID 7031 Next: How to move all dbo schema objects to another schema? |