Problem developers upgrade from SQL Server 6.5 to SQL
What are the issues that Server 7.0 should be aware of?
For the general account, the upgrade program works very well. In the process of upgrading to SQL Server 7.0, SQL
Aliases in Server 6.5 are converted to role members. For example, if one of your aliases is "dbo",
SQL Server will take you as a member of the "db_owner" fixed server role. If you have several login aliases that are "sales",
The SQL Server 7.0 upgrade creates a "sales" role, creates accounts for those users, and then puts them all into the sales role. Sql
The "Sales" account in Server 6.5 has permissions that are assigned to the sales role in SQL Server 7.0. SQL in the upgrade process
The server also cleans up the mode bits of system permissions to eliminate duplicate permissions.
The more difficult scenario is that someone might restore 10 databases from different machines to a single SQL
Server, and then upgrade. In SQL Server 6.5, user accounts must match the "syslogins" system tables in the master database and the "sysusers" system tables in each database. Unfortunately, when you're in the SQL
When restoring databases from other servers in Server 6.5, they cannot be matched. When the database is restored in this case, its security system is corrupted and upgraded to SQL
is still corrupted after Server 7.0.
The trick is to make sure your SQL Server 6.5 system is working well before upgrading. You should also run the "sp_change_users_login" System stored procedure before you upgrade to ensure that the correct mapping relationship exists between the database user and the login name.
What is the best way to build a secure database using SQL Server 7.0?
Never give users permission to access the table directly. If you want users to use interactive tools such as Microsoft
Acess 2000来 access to the database, you can give them access only to views and stored procedures, not directly to the table. If the owner of the stored procedure is "dbo" and the owner of the tables and views referenced by the stored procedure is also "dbo", giving the user execute (EXECUTE) permission on the stored procedure is sufficient. This does not have to check the access to the table at all.
You can also use other security features, such as controlling which fields or rows can be accessed by adding business logic to the stored procedure. Views are another way to prevent users from accessing a table directly. The difference from a stored procedure is that you can grant a SELECT, INSERT, update, or delete permission to a view, whereas a stored procedure can only grant execute permission.
Another thing to note is that if you execute a SELECT statement in another database, the chain relationship of the owner of the database object still applies. For example, in the SQL
In the pubs database owned by the server logon name "SA", you cannot perform a crosstab query to connect to a database owned by an NT login, even if two logins are members of the sysadmin role. If you want to connect a table from 3 different databases, the owner of these 3 databases should be the same account number. You can use the stored procedure "sp_changedbowner" to change the owner of the database if needed.
