SQL Server Add user Add Role Assignment permissions

--Create a simple login with: newlogin; login password: 123456; default database: Master, default database can also not be specified.

EXEC sp_addlogin ' newlogin ', ' 123456 ', ' master '

--Create User

--Create a simple user that, if you do not specify a user name, is added to the current database login, and if you do not specify a role, the user defaults to the public role. Next to add the Newlogin login name.

EXEC sp_adduser ' Newlogin '

--Create a user with a user name, the user can be the same as the login name (similar to the same), or different, but to set the current login, the user role is optional, the default is public. To add the user NewUser to the newlogin login name.

EXEC sp_adduser ' newlogin ', ' NewUser '

--Create a role

EXEC sp_addrole ' Newrole '

--Next to add user NewUser to the newlogin login name. and specify the Newrole role.

EXEC sp_adduser ' newlogin ', ' newuser ', ' newrole '

--Give all permissions to the jobs table for the role Newrole

GRANT all on jobs to Newrole

--Give the sales table the check and change permissions for the role Newrole

GRANT select,update on sales to Newrole

--Prohibit role newrole INSERT permission using Employees table

DENY INSERT on employees to Newrole

Another way to create users and assign roles

--Add a security account to the database for login Newlogin NewUser

EXEC sp_grantdbaccess ' newlogin ', ' NewUser '
--Add NewUser as a member of role Newrole
EXEC sp_addrolemember ' newrole ', ' NewUser '

--Delete operations for database users, roles, logins

--Delete the current database user

EXEC sp_revokedbaccess ' NewUser ';

--Delete Database login

EXEC sp_droplogin ' Newlogin '

--Deleting database roles

EXEC sp_droprole ' Newrole '

--Remove User (NewUser) from database role (Newrole)

EXEC sp_droprolemember ' newrole ', ' NewUser '

--New login with SQL code, user

--Create a mylogin login with a password, the Must_change option requires the user to change this password the first time the server is connected.

CREATE LOGIN mylogin with PASSWORD = ' 123456 ' Must_change;

--Create a login that maps to the credential.

--The following example creates a MyLogin login name. This login is mapped to the mycredential credential.

CREATE LOGIN mylogin with PASSWORD = ' 123456 ',

credential = mycredential;

--Create a login from a Windows domain account

--If the login is mapped from a Windows domain account, the login must be enclosed in square brackets ([]).

CREATE LOGIN [Jack\xiangzhao] from WINDOWS;

--If a user name is specified, the default login name is not used as the database user

CREATE USER myuser for LOGIN MyLogin

--The following example creates a database role owned by the user MyUser Myrole

CREATE ROLE myrole AUTHORIZATION myuser;

--The following example creates a database role owned by the Db_role fixed database role Myrole

CREATE ROLE myrole AUTHORIZATION db_role

SQL Server Add user Add Role Assignment permissions