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_addlogin ‘login_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).
Leave Comment