how to change the name of default schema from dbo to say test1, or else
create another schema say test2 and assign all the user tables to this
schema.

how to change the name of default schema from dbo to say test1. I tried
doing it using the command

ALTER schema testschema1 transfer table1

but i get the following error.

Msg 15151, Level 16, State 1, Server PC14\SQLEXPRESS, Line 1
Cannot find the object 'table1', because it does not exist or you do not
have
permission.

please help.

S N (uandme72 (AT) invalid (DOT) com) writes:
You cannot rename the dbo schema. In fact, I don't think you can
rename schemas at all. At least I can't find out how to do it.

Well, you cannot transfer a table that does not exist. I'm a little
uncertain what you really want to due, but maybe this script will help
you out:

CREATE TABLE thistable (a int NOT NULL)
go
SELECT * FROM dbo.thistable
go
CREATE SCHEMA newschema
go
ALTER SCHEMA newschema TRANSFER dbo.thistable
go
SELECT * FROM newschema.thistable
go
DROP TABLE newschema.thistable
go
DROP SCHEMA newschema


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx

Actually I want to transfer all the tables in the schema named dbo to
another schema which I create as per my choice.
Subsequently I should be able to log into the SQL server in such a way that
the default schema being accessed is the new schema created by me and not
the dbo schema alongwith being able to execute all the database operations
as required.
Please advise.

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

S N (uandme72 (AT) invalid (DOT) com) writes:
SELECT 'ALTER SCHEMA newschema TRANSFER dbo.' + quotename(name)
FROM sys.objects
WHERE schema_id = 1

Copy and paste results into a query window and run.

ALTER USER xxx WITH DEFAULT_SCHEMA = newschema

xxxx is here your username in the database. Note here if the SQL Server
login that you use owns the database, your database user is dbo, in
which case the above command will not work, because you cannot change
the dbo user.

Furthermore it will not work if your Windows login belongs to the
server role sysadmin, because again your login will map to dbo.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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/prodinf...ons/books.mspx