Home > DeveloperSection > Blogs > Create User Login in SQL Server 2008 R2

Create User Login in SQL Server 2008 R2

Posted by  Vijay Shukla
on    December-04-2012 8:37 AM

Database Database 
Ratings:
0 Comment(s)
 4818  View(s)
Rate this:

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).