T-SQL configuration database information

If you are using the MSSQL database of a VM, you do not need to configure too much, because the main configuration work needs to be completed on the server, the options you currently have are used by the webmaster for routine maintenance. The following method requires operations by your VM provider. You can try it out, I have always used MYSQL, so I am not very familiar with MSSQL. I don't know if I can help you! The single line split line is another solution. You can also refer to it.

* ***** Method 1 *****
Cause:

Restoring a database backup to another server may cause isolated users;

Solution:

Steps:

1. restore the backed up database to the new database server (1.1 Enterprise Manager --> 1.2 database (right-click) --> 1.3 all tasks --> 1.4 Restore database --> 1.5 General/restore to database/write your database name xxxx/select from device/select device/Add (browse to find your database Backup files) /OK --> 1.6 option --> 1.7 restore the database to f: \ usr \ xxxx. mdf is generally stored in the directory where your SQL is installed, for example, d: \ Program Files \ Microsoft SQL Server \ MSSQL \ Data \ xxxx. mdf --> 1.8 OK !!
2. view the users in the database you just restored, such as abc;
3. check whether there are any login users under security: If abc does not exist, abc is an isolated user;
4. Open the query analyzer running script
Use Database Name
Go
DECLARE @ sid BINARY (16)
SELECT @ sid = sid FROM sysusers WHERE name = 'abc' and islogin = 1
Exec sp_addlogin @ loginame = 'abc', @ sid = @ sid
5. note: It should be restored first. If you have already created the abc logon in SQL security-Logon, delete it first and then execute the preceding statement. the problem can be solved in the same way! ========================================================== ====================================
Summary: isolated user troubleshooting
When restoring a database backup to another server, you may encounter isolated users. The following solution solves the problem:

Execute sp_addlogin to change the logon janetl name to dbo.
Sp_addlogin 'Janet', 'dbo'
Back up the database. In this example, back up Northwind.
Backup database Northwind
To disk = 'C: \ mssql \ backup \ northwnd'
Remove the database you just backed up.
Drop database Northwind
Except logon.
Sp_droplogin 'Janet'
Restore the backup database.
Restore database Northwind
From disk = 'C: \ mssql \ backup \ northwnd'
Janetl login cannot access the Northwind database, unless guest Login is allowed. Although the janetl login has been deleted, it is still (as an isolated row) displayed in the sysusers table:
USE Northwind
SELECT *
FROM sysusers
WHERE name = 'Janet'
Solve isolated user problems
Use sp_addlogin to add a temporary logon. Specify the security identifier (SID) (from sysusers) for an isolated user ).
Sp_addlogin @ loginame = 'nancyd', @ sid = 0x32c864a70427d211b4dd0020.b9e8a00
Use sp_dropalias to remove the temporary alias belonging to the alias SID.
Sp_dropalias 'nancyd'
Use sp_dropuser to remove the original user (that is, the current isolated user ).
Sp_dropuser 'Janet'
Use sp_dropuser to remove the original logon.
Sp_droplogin 'nancyd'

==========================================================
Demonstration of isolated user generation
Create a Test Database
Create database DB_test
Go
Create a login
EXEC sp_addlogin 'A'
Set the default database for logging on to aa as the test database DB_test.
EXEC sp_defaultdb 'A', 'db _ Test'
Go
Switch to test database
USE DB_test
Go
Add a user to the current test database to log on to aa
EXEC sp_grantdbaccess 'A'
Go
At this point, the default database of user aa after logon is DB_test.
We can log on to the query analyzer using user aa to verify our test environment.
Back up the test database to prepare for the following tests.
{
Backup database DB_test to disk = 'C: \ DB_test.bak 'WITH INIT
Go
/* = ======= */
Switch to master database
USE master
Go
Delete Test Database
Drop database DB_test
Go
Delete logon aa
EXEC sp_droplogin 'A'

Go
/* = ========= */
Restore Test Database
Restore database DB_test from disk = 'C: \ DB_test.bak'
Go
Switch to test database
USE DB_test
Go
View user information
Select name from sysusers where islogin = 1
We will find that, although we have deleted the logon aa, user aa still exists in the Database

Try again. Log On with aa and be notified of Logon Failure
Go
Then add the deleted login back.
EXEC sp_addlogin 'A'
Set the default database for logging on to aa as the test database DB_test.
EXEC sp_defaultdb 'A', 'db _ Test'
Log on again and be notified that the default database cannot be opened. Logon Failed
Go
Change the default database to master.
EXEC sp_defaultdb 'A', 'master'
You can log on again this time.

Go
Try to switch to the test database DB_test
USE DB_test

Error message: the server user 'A' is not a valid user in the database 'db _ test.
It seems that the connection between user aa and logon aa is lost.
Go
Try again to add user aa for login aa
EXEC sp_grantdbaccess 'A'
Error message: the current database already has a user or role 'A '.
In another order, we will first create a login and then restore the database to see if the login can be automatically established with the user.
Before you perform this test, first clean up the test environment, that is, perform the previous <generate isolated user> step, and then start the test.
Add logon first
EXEC sp_addlogin 'A'
Go
Restore Test Database
Restore database DB_test from disk = 'C: \ DB_test.bak'
Go
Switch to test database

USE DB_test
Go

View user information
Select name from sysusers where islogin = 1
We will find that user aa exists in the Database
Try again, log on with aa, and switch to DB_test
The result is that the logon is successful, and the same error occurs when accessing DB_test as that in Test 1.
For SQL Server Error '80040e37'
The object name 'DV _ user' is invalid.

/***. Asp, line *

First, I checked the database table. This table exists, and the calling statements in the program are correct.
After rewriting the code, the result is still the same.
If you suspect that it is not a program problem, you can view other pages and find that all pages have similar problems. If you call the database, errors will occur. it is determined that there is a problem with the database. the database link is correct. import is correct.
Go solved the problem.

Change the owner of all tables to DBO.
Run the following statement to change the owner of all tables to DBO.
Exec sp_msforeachtable "sp_changeobjectowner '? ', 'Dbo '"

* There are some twists and turns in how to modify the database owner, which are summarized as follows:

1: Run sp_changeobjectowner 'table name' and 'dbo;
2. Change the owner of all tables to dbo, which can be processed cyclically;
3. In fact, the SQL System storage process sp_MSForEachTable can be easily done, just run
Exec sp_MSForEachTable 'SP _ changeobjectowner ''? '', ''Dbo '''.
Of course, errors may occur during running. (For example, if the owner of some tables is dbo, the system prompts "because the new owner 'dbo' already has an object with the same name ."), Run the statement again.

Certificate ------------------------------------------------------------------------------------------------------------------------------------
* ***** Method 2 *****

Microsoft VBScript compiler error: '800a03f6'

'End' missing'

/IisHelp/common/500-100.asp, row 242

Microsoft ole db Provider for SQL Server Error '80040e37'

The object name 'x _ Siteconfig 'is invalid.

/Rc/conn. asp, Row 16

First, I checked the database table. This table exists, and the calling statements in the program are correct.

After rewriting the code, the result is still the same.

If you suspect that it is not a program problem, you can view other pages and find that all pages have similar problems. If you call the database, errors will occur.

The database is faulty. The database link is correct.

Import is correct.

Baidu:

Change the owner of all tables to DBO.

How does SQL change the owner of all tables?
The SQL statement for modifying an owner is as follows:

Query analyzer input: EXEC sp_changeobjectowner 'user. table', 'dbo'

User. table indicates the owner. table name, such as oblog. oblog_user. The previous statement indicates that

Change user to dbo

The statement for modifying owners in batches is as follows:

Query analyzer input: exec sp_msforeachtable 'SP _ changeobjectowner "? "," Dbo "'

You can replace all the owner of the face-to-face table with dbo.