Creating a database in SQL Server 2005

Question

After installing SQL Server 2005, I tried to create a new database (command: create database database_name). But the message was an error: permission denied to create a database in 'master' database. My question is 1.) how to exit this master database and create a new database outside it, 2.) what is a master database.

Answer 1

You need to Log in using sa account (system administrator), which should have all permissions. From there, you can create additional accounts as needed.

To create a new database, the easiest way is to use SQL Server Management studio, and you can right click on "Databases" (in the left pane) and choose to create a new database.

The "master database" just basically contains the metadata information (i.e. info about all the other DBs, etc.)

Answer 2

SQL Server maintains lot of meta-data about SQL Server, databases, files, logins etc in the master database. Master database is the heart & key for SQL Server to work properly.

One has to use master database to create new user generated databases. It looks like the login you are using doesn't have permissions to the master database or doesn't have permissions to create a new database. Make sure you have enough permissions to create the database and add yourself to db_creator role or the higher level sysadmin role.

I wouldn't recommend you to use SSMS to create a new database. I would use the T-SQL script as you will have more control. Once you have created the database, make sure you have turn-offed AUTO SHRINK. Also make sure you have set the initial file size large enough so that the files will NOT auto-grow. Also make sure you have set the auto-grow long enough so that it won't grow in small increments.

Answer 3

The master database is the default database to which you log in. It sounds like you don't have permissions set up. The easiest fix to just get started is to run Mgmt Studio as admin. For the right way to modify permissions, look here: http://msdn.microsoft.com/en-us/library/bb326612.aspx .