Solved

SQL Server 2008 Database Creation Syntax with username and password

Posted on 2011-03-03
5
504 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

710 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