Solved

SQL Server 2008 Database Creation Syntax with username and password

Posted on 2011-03-03
5
497 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

911 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

17 Experts available now in Live!

Get 1:1 Help Now