January 2000 Web Exclusive SQL Server Q & A

Answers from Microsoft

My problem involves SQL Server logins. AccessUsers is the name of the global group on Windows NT. I granted the global group access to SQL Server. Also, I created a database role called UseAccess, which has permissions to the Access97 migrated files. I assigned the role UseAccess to the group AccessUsers. The global group needs to use the sa login account to access the database. If I choose the Use trusted domain check box during SQL Server login, the login credentials dialog box reappears and continually asks for a login account. The users still can't access the database unless they use the sa login account.

I created the database (migrated from Access97) on SQL Server under the administrator account. The NT global group exists; it’s called SomeDom\AccessUsers. I ran sp_grantlogin \[SomeDom\AccessUsers\] to give all AccessUsers a login directly to SQL Server. Then I ran

sp_grantdbaccess \[SomeDom\AccessUsers\]

to give the users access to the database. I then created a role called UseAccess (sp_addrole 'UseAccess'), ran

sp_addrolemember 'UseAccess',\[SomeDom\AccessUsers\]

and logged into NT as a member of the group SomeDom\AccessUsers. Why won’t this procedure work?

First, you need to check for a login deny entry. Someone might have tried to deny SomeDom\AccessUsers the right to log in. You can check for any type of deny entry in the logins page by going to Enterprise Manager and opening the Security entry. Then right-click on each login, and select Properties from the pop-up menu. The deny access entry is on the General tab. Any single deny can prevent a user from logging in. So, if your users log in using their AccessUsers group membership, but you deny the Domain users, no one can log in except the sa.

On SQL Server 6.5, can I create Transact SQL (T-SQL) syntax in a job to retrieve the latest full backup to restore data, and how can I retrieve the correct file number? I have two SQL Servers, production and test. I created a job to back up the database without the init syntax option in the backup command on the production server backup device, so the backups (including full and transactional backup) will append the database to the device.

On the test server, I used the query


to automatically restore the latest full backup from the backup device on the production server. But, if the production server doesn’t make a transaction log backup, I’ll restore the wrong file because the number for the file isn’t in the same order on the device that I want.

Use the LOAD HEADERONLY command:


This command gives you the backups that are on a backup device, and their type and file number.

Can I update, insert, and delete data on SQL Server 7.0 and replicate it to Oracle? Also, can I update, insert, and delete data from Oracle and replicate it to SQL Server 7.0?

Also, can I update, insert, and delete data on a SQL Server 7.0 and replicate it to another SQL Server 7.0 and vice versa?

Yes, you can replicate data on Oracle from SQL Server. SQL Server 7.0 supports both transactional and snapshot replication to Oracle when SQL Server is acting as the information publisher. However, you’ll need Oracle’s software for an Oracle database to publish Oracle data to SQL Server. Microsoft doesn’t provide or support the Oracle software required to implement Oracle database replication.

You can also update, insert, and delete data on SQL Server 7.0 and replicate it on another SQL Server 7.0. See the section on replication in SQL Server Books Online (BOL) for details.

After upgrading from SQL Server 6.5 to 7.0, I ran sp_help on a table named wo_recipient. Why did it create more indexes on the table, and how can I prevent it?

The old index names are:


The new indexes created by SQL Server are:


These listings come from the auto-statistics function. You can turn off auto-statistics by opening the Enterprise Manager and right-clicking on the appropriate database name. Then select the Properties option from the pop-up menu. The Auto Create Statistics property is on the Options tab. However, I recommend that you leave it on because the Query Optimizer uses statistics that SQL Server creates to help resolve a query. Performance can suffer without statistics. Note that the information appears as indexes in some reports, but they’re not considered indexes; it’s statistical information about the data in those columns. For more information on statistics generated from the Query Optimizer, see Kalen Delaney’s article "SQL Server Statistics: A Useful Query Optimizer Tool" (August 1999).

My database has a lot of identity-column-based tables in a multiple-database environment. Can merge replication solve the problem of replicating changes from the databases so I can keep the identity values unique on all servers, and if so, how?

Identity columns might not be unique between servers, which can cause replication problems. If you set the identity seed to a different value on each server, you avoid potentially overlapping the identity values between SQL Server systems. Because identity columns are server-specific, you need to range partition them to use as keys or as another unique identifier. For example, on a server, set a range of identities, such as

pk_col int identity (1,1)

and on another server start the identity columns at 10000

pk_col int identity (10000,1) 

This example assumes you have the maximum number of identities possible on each server. You can get around this to a certain extent (use Numeric(28) instead of int for example), but if you don't know the max number, try using the UniqueIdentifier data type, a global unique identifier (GUID).

How do I backup a SQL Server 6.5 database to another server?

For example, when you run


SQL Server adds a logical backup device named remote_backup of the type disk on the server remoteserver into a file share (which I called remoteshare) that you need to create.

You can also add to the backup device a Uniform Naming Convention (UNC) share that you can backup to across a network through Enterprise Manager. You need to make sure the MSSQLServer service account has access to the file share and full control access to add and modify files. Also, you must use the service account and not the localsystem account when you run SQL Server under the LocalSystem account. Using a standard configuration, SQL Server can’t transfer data across the network to backup a device on a remote UNC share when the local SQL Server service is running under the LocalSystem account.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.