SQL Create user script

Use master

Go CREATE LOGIN Jiazhuang--User name

with PASSWORD = ' sa ',--password

Default_database = Jiazhuan,--database name

Check_expiration = OFF,

Check_policy = OFF

Go

EXEC sp_addsrvrolemember ' Jiazhuan ', ' sysadmin '--character

Go

To successfully access data in a SQL Server database, we need two authorizations: one, the right to be granted access to the SQL Server server; Second, the right to accessing data in a particular database (SELECT, Update, delete, CREATE table ... ）。 Suppose we are going to set up a DBA database account to manage database MyDB.

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

Create login DBA with password= ' Sqlstudy ', default_database=mydb

Login account Name: "DBA", Login Password: "Sqlstudy", 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 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:

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:

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!

4. Disable and enable login account:

ALTER LOGIN DBA Disable
ALTER LOGIN DBA Enable

5. Renaming the login account:

ALTER LOGIN DBA with Name=dba_tom

Tip: You can also rename the SA in SQL Server 2005. SQL Server 2005 Security Enhancements: Renaming super users SA

6. Login Account Change Password:

ALTER LOGIN DBA with password= ' sqlstudy.com '

7. Renaming the database User:

Alter user DBA with Name=dba_tom

8. Change database user defult_schema:

Alter user DBA with Default_schema=sales

9. Delete Database users:

Drop User dba

10. Delete the SQL Server login account:

Drop Login DBA

SQL Create user script