"Go" Create a user role and authorization (using SQL statements) in SQL Server

1. Start the login account at the SQL Server server level (create login)

--Creating login account (Create login)

Create login DBA with password= ' [email protected] ', Default_database=mydb

Login account Name: "DBA", login password: [email protected] ", the default connection to the database:" MyDB ". At this point, the DBA account can connect to the SQL Server server. However, it is not possible to access objects in the database at this time (strictly speaking, the DBA account defaults to the guest database user identity and can access the database objects that the guest can access).

To enable the DBA account to access the objects it needs in the MyDB database, you need to establish a "database user" in the database mydb, give this "database user" some access rights, and map the login account "DBA" to this "database user". In practice, the name "database user" is the same as the name of "login account", i.e. "DBA". Creating a database user and establishing a mapping relationship can be done in just one step:

2. Creating a database User (create user):

--Create a database user for the login account, the newly created DBA can be found under user in security in the MyDB database

Create user DBA for login DBA with default_schema=dbo

and specifies that the default schema for the database user "DBA" is "dbo". This means that the user "DBA" executes "SELECT * from T" and actually executes "select * from DBO.T".

3. Give the database user "DBA" permission by joining the database role:

--Give database user "db_owner" permission by adding database role

exec sp_addrolemember ' db_owner ', ' DBA '

At this point, the DBA has full control over the objects in the database MyDB.

If you want the SQL Server login account "DBA" To access multiple databases, such as MYDB2. You can have the SA execute the following statement:

--Let the SQL Server login account "DBA" Access to multiple databases

Use MYDB2
Go create user DBA for login DBA with default_schema=dbo
GO EXEC sp_addrolemember ' db_owner ', ' DBA ' Go

At this point, the DBA can have two database mydb, MYDB2 administrative rights!
Complete code example

--Create DATABASE MyDB and mydb2--create test tables in MyDB and MYDB2, the default is dbo this schemacreate TABLE DEPT (DEPTNO int primary key, Dname VARCHAR (1 4), LOC VARCHAR (13));--inserting data insert into DEPT values (101, ' ACCOUNTING ', ' NEW YORK '); insert into DEPT values (201, ' "DALLAS"); insert into DEPT values (301, ' SALES ', ' CHICAGO '); insert into DEPT values (401, ' OPERATIONS ', ' BOSTON ');--View database schema, user's stored procedure select * FROM Sys.database_principalsselect * from Sys.schemas select * from Sys.server _principals--Create login account (Create login) Create login DBA with password= ' [email protected] ', default_database= mydb--creates a database user for the login account (create user), under User in security in the MyDB database, can find the newly created dbacreate User DBA for login DBA with default_schema= dbo--by adding database roles to the database user "db_owner" permissions exec sp_addrolemember ' db_owner ', ' DBA '--Let SQL Server login account "DBA" Access multiple databases using Mydb2go Create user DBA for login dba with Default_schema=dbogo exec sp_addrolemember ' db_owner ', ' DBA ' go--disable login account ALTER LOGIN DBA D isable--Enable login account ALTER LOGIN DBA enable--login account rename Alter Login DBA with name=dba_tom--login account Change Password: Alter LOGIN DBA with password= ' [email protected] '--database user renamed: Alter User DBA WIT H name=dba_tom--Change database user Defult_schema:alter user dba with default_schema=sales--Delete database users: Drop user dba--Delete SQL Server login account : Drop Login DBA

"Go" Create a user role and authorization (using SQL statements) in SQL Server