Blog
    C#
    ADO.Net
    .NET
    ASP.Net & Web Forms
    Custom Controls
    Web Development
    Exception Handling
    XML
    Database
    Security in .Net
    Testing
    Web Services
    Windows Services
    Windows Controls
    WCF
    AJAX
    WPF
    XAML
    Reporting
    Setup
    VB.Net
    LINQ
    JQuery
    SilverLight
    JavaScript
    HTML5
    Crystal Report
    Cloud Computing
    Share Point
    Visual C++
    MVC
    Android
    PHP
    Java
    HTML
    WordPress
    Joomla
    Products
    Drupal
    Windows Phone
    JSON
    LightSwitch
    iPhone/iPad
    Ruby on Rails
    IIS 7
    Windows 8
    CSS/CSS3
    Excel
    MS Access
    Shortcut Keys
    Visual SourceSafe
    Team Foundation Server
    API(s)
    Sencha-Touch
    Single Page App
    Bootstrap
Follow Us
Follow _MindStick_ on Twitter View MindStick Software's LinkedIn profile View MindStick Software's Facebook profile
Top Contributor
Advertisement
Advertise with Us
Mindstick
Article Article  Forum Forum  Blog Blog  Quiz Quiz  Beginner Beginner  Careers Careers  Contact Contact  Login Login  
Home | Product | Services | About Us | Interview | DeveloperSection | Submit an Article | Submit Blog
Report Abuse Form
Reason:    
 

Home >> Database >> Create User Login in SQL Server 2008 R2
Create User Login in SQL Server 2008 R2

How to created user login in sql server 2008 R2 through the Query.
Views: 4231     Comments: 0
by Vijay Shukla on 12/4/2012

Create User Login in SQL Server 2008 R2

Create user with the query

                                         

CREATE LOGIN [Login_Name] WITH PASSWORD='password' MUST_CHANGED, DEFAULT_DATABASE=[your_database], DEFAULT_LANGUAGE= [language],CHECK_EXPIRATION={OFF/ON}, CHECK_POLICY={OFF/ON}

Login_Name:- Specifies the name of the login that is created.

PASSWORD: - Applies to SQL Server logins only. Specify the password for the login that is being created.

MUST_CHANGED:- If this option is included, SQL Server prompts the user for a new password the first time the new login is used.

DEFAULT_DATABASE:- Specifies the default database to be assigned to the login. If this option is not included, the default database is set to master.

DEFAULT_LANGUAGE:- Specifies the default language to be assigned to the login.

CHECK_EXPIRATION:- Specifies whether password expiration policy should be enforced on this login. By default it’s OFF.

CHECK_POLICY:- Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. By default it’s ON.

Create login User through Procedure

EXEC sp_addloginlogin_name','password','Default_database','default_language'

Add Server Role in Login User:-

EXEC sp_addsrvrolemember [@loginame=] 'login', [@rolename =] 'role'

Role Names:- 

                                                        I.            sysadmin

                                                      II.            securityadmin

                                                    III.            serveradmin

                                                    IV.            setupadmin

                                                      V.            processadmin

                                                    VI.            diskadmin

                                                  VII.            dbcreator

                                                VIII.            bulkadmin

                                                    IX.            public

Create Role member permission:-

CREATE ROLE role_name

GRANT SELECT,INSERT,UPDATE,DELETE  ON dbo.Table TO role_name;

role_name:- Specifies name of the ROLE which is created.

dbo.Table:- Specifies the Table object name.

Drop Role:-

            DROP ROLE role_name

Give the permission for Login User:-

            GRANT create permission to login_name

Permission:-

1)  ADMINISTER BULK OPERATIONS

2)  ALTER ANY CONNECTION

3)  ALTER ANY CREDENTIAL

4)  ALTER ANY DATABASE

5)  ALTER ANY ENDPOINT

6)  ALTER ANY EVENT NOTIFICATION

7)  ALTER ANY LINKED SERVER

8)  ALTER ANY LOGIN

9)  ALTER ANY SERVER AUDIT

10)ALTER RESOURCES

11)ALTER SERVER STATE

12)ALTER SETTINGS

13)ALTER TRACE

14)AUTHENTICATE SERVER

15)CONNECT SQL

16)CONTROL SERVER

17)CREATE ANY DATABASE

18)CREATE DDL EVENT NOTIFICATION

19)CREATE ENDPOINT

20)CREATE TRACE EVENT NOTIFICATION

21)EXTERNAL ACCESS ASSEMBLY

22)SHUTDOWN

23)UNSAFE ASSEMBLY

24)VIEW ANY DATABASE

25)VIEW ANY DEFINITION

26)VIEW SERVER STATE

Example:-

                                GRANT create ANY DATABASE to mylogin

( Now in mylogin we can create any database).

Title :  
Comment :
Text ColorBackground Color
BoldItalicUnderline
LeftCenterRightJustify
Ordered ListBulleted List
IndentOutdent
Horizontal Rule
SubscriptSuperscript
HyperlinkImage
Design ModeDesign
View HtmlHtml
       
 
Report Abuse Form
Reason:    
 
Latest ArticleRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest Blogs by Vijay ShuklaRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 5041
  
Copyright © 2009 - 2014MindStick. All Rights Reserved.