Solved

SQL Server 2008 Database Creation Syntax with username and password

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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.
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 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