Solved

SQL Server 2008 Database Creation Syntax with username and password

Posted on 2011-03-03
5
501 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
ID: 35032498
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
ID: 35034347
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
ID: 35037388
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
ID: 35065795
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
ID: 35183229
good
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

820 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