Solved

SQL Server 2008 Database Creation Syntax with username and password

Posted on 2011-03-03
5
496 Views
Last Modified: 2012-05-11
Hi all,

I have some questions regarding databases.

I would like to know the exact command of creating a database with user name and password using SQL Server 2008.
I have used the following command syntax and it works fine but not sure if this is correct syntax:

CREATE DATABASE [Test_DB] ON
( NAME = 'Test_DB', FILENAME = 'C:\Program Files\Microsoft SQL

Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_DB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB

)
 LOG ON
( NAME = 'Test_DB_log', FILENAME = 'C:\Program Files\Microsoft SQL

Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Test_DB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

CREATE LOGIN user
WITH PASSWORD = 'password',
DEFAULT_DATABASE= Test_DB

GO


Another question is where does the user and password are stored and how can I locate them in SQL Server 2005 or 2008?

If I delete the above database, does the user and password are deleted as well? If not why?

Thanks in advance. Looking forward to your replies.
0
Comment
Question by:Itudk_2010
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
ewangoya earned 250 total points
Comment Utility
Your statements are syntactically correct

You will not be able to see your password but to see the login user you created, open SSMS (Management Studio)
Expand Security under object inspector, you will see Logins
Expand Logins to see the user you created

If you delete the database, the login user and password are not deleted.
The reason being, a login user is used to access the server so it can be used for multiple databases, not just a single database.
0
 
LVL 9

Expert Comment

by:kaminda
Comment Utility
Actually the database creation script does not require to create an account. Creating an account and assigning access rights is a different thing. The confusion here has came from that I guess. You done need that part of the script in the database creation.

In SQL Server users are managed seperately regardless of the database. Users are common to the whole server only limited by their privilages. So I hope you get the point by now why the users are not deleted when a database is deleted.
0
 

Author Comment

by:Itudk_2010
Comment Utility
Thanks for your replies.
Is the above database creation command correct? What is the difference between user and login?

If I want to create a database user for only the above database, can I use the following or creating user for a particular database is different?

CREATE user user1
WITH PASSWORD = 'password',
DEFAULT_DATABASE= Test_DB

Thanks  and looking forward to your replies.
0
 
LVL 9

Assisted Solution

by:kaminda
kaminda earned 250 total points
Comment Utility
Your create database script is correct. But there are lot of things you can add to that which are optional. Simply a script like "CREATE DATABASE test" will create a database using defaults for parameters.

Login are acutally for entire SQL Server instance where as users are for a database. But to create a user you must associate a login to that.  In the below example it creates a login and create a user for adventureworks2008r2 using that login.

CREATE LOGIN AbolrousHazem
    WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
USE AdventureWorks2008R2;
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;
GO


In your code it will create a login for entire database. But the defult database would be test_db.

 
0
 

Author Closing Comment

by:Itudk_2010
Comment Utility
good
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now