Tuesday, December 13, 2011

How to create tables in a schema other than dbo schema by default

First create Login if you don’t have already:

CREATE

LOGIN [best] WITH PASSWORD=N'best', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

then create user named ‘best’ with below command in the database you need. Login gives access only to server if you need to access a database you need to have that user in the DB

"CREATE

USER [best] FOR LOGIN [best] WITH DEFAULT_SCHEMA=[Test]"

make sure you give permission to this user 'best' to create tables.

Now login to server as user ‘best’ and create a table named ‘Test’ in the DB. And you will notice the table is created in Test schema by default.

Thanks

2 comments: