Tips from the SQL Server MVPs - 02 Mar 2001

Editor’s Note: Welcome to SQL Server Magazine’s monthly, Web-only series of SQL Server tips brought to you by the Microsoft SQL Server Most Valuable Professionals (MVPs). Microsoft introduced the MVP program in the 1990s to recognize members of the general public who donate their time and considerable computing skills to help users in various Microsoft-hosted newsgroups. MVPs are nominated by Microsoft support engineers, team managers, and other MVPs who notice a participant's consistent and accurate technical answers in various electronic forums and other peer-to-peer venues. For more information about the MVP program, go to http://support.microsoft.com/support/mvp/. The MVPs donate their SQL Server Magazine author fees for these tips to the World Food Programme (http://www.wfp.org). To donate free food to a hungry person today, visit http://www.thehungersite.org.

How can I use Visual Basic (VB) to return a list of SQL Servers registered locally in Enterprise Manager?

To return the list of servers registered locally in Enterprise Manager, you can use the ServerGroups and RegisteredServers collections from SQL Distributed Management Objects (SQL-DMO). The code in Listing 1 demonstrates how to use these collections. Note that to use SQL-DMO from VB, you need to set a reference to the Microsoft SQL-DMO Object Library by opening the VB Project menu, selecting the References option, selecting Microsoft SQL-DMO Object Library from the list in the dialog box, and clicking OK.

How do I remove replication settings after I disable publishing?

SQL Server is notorious for leaving replication settings behind when you disable publishing. The settings that get left behind don’t interfere with your databases’ operation except when you want to drop a database or drop or alter a table that was previously published or subscribed. The solution to the table problem lies in the sysobjects system table in a column called replinfo. For tables that aren’t involved in replication, this value is set to 0. For tables that are involved in replication, this column is set to 128. The database engine checks this column, and if the column is set to 128, SQL Server rejects the drop or alter action for that table. To clean up the replinfo setting, run the script that Listing 2 shows. I can never remember which options require you to issue a reconfigure command, so I always issue a reconfigure command. It won’t have side effects if it wasn't needed.

To remove the database replication settings, you simply need to turn off the three options that apply to replication—merge publish, published, and subscribed. The script in Listing 3 will disable these options.

Here’s a final note for Enterprise Manager users: Enterprise Manager caches information on the client. So if you tried to drop or alter a table and SQL Server prevented that action because replication settings were still enabled, you won’t be able to drop or alter that table even after you turn off those settings unless you refresh Enterprise Manager.

I locked myself out of SQL Server without being able to log on with a sysadmin login. Can I fix this, or do I need to reinstall SQL Server?

You don’t have to reinstall SQL Server. To access SQL Server, you need to use the registry key for SQL Server 2000 and 7.0 that determines the authentication mode of SQL Server.

In SQL Server 7.0, the key is:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerLoginMode

In SQL Server 2000, the key is:

HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQLServerMSSQLServerLoginMode

The value of LoginMode is 0 for Windows Authentication only, and 1 for Mixed Mode. After locking yourself out, you can change the value of LoginMode to 0, restart SQL Server, and log in as sa.

The following information about SQL Server role memberships might help you understand how you locked yourself out. When you install SQL Server 2000 or 7.0, the installation process automatically creates a login for BUILTINAdministrators with sysadmin server role membership. The BUILTINAdministrators login stands for the Administrators local group in your Windows 2000 or Windows NT server. The Win2K or NT administrator account is a member of the Administrators local group by default. Also, if your server is a member of a domain (both in Win2K and in NT 4.0), the global group Domain Admins becomes a member of the local Administrators group, too. This means that all members of the Administrators local group automatically gain sysadmin rights in your SQL Server.

To tighten security for your SQL Server, you might prefer to create your own group and map it to a login with sysadmin rights to your SQL Server machine and remove the BUILTINAdministrators login, or at least remove it from the sysadmin server role. This way, you'll have better control of who gains sysadmin rights in your SQL Server. This approach also breaks the relationship between SQL Server administrators and Win2K or NT administrators, who usually have different tasks and need different rights.

As another step in tightening security, you might want to configure your SQL Server to support only Windows Authentication. However, keep in mind that this configuration will disable your sa account. (This method is probably the only way to disable the sa account because you can't delete the sa account.) If you implement these security measures in the wrong order, you won’t be able to log on to SQL Server as a sysadmin without using the key I described. The correct order is

However, if you implement the security steps in the wrong order—by deleting the BUILTINAdministrators login, changing your SQL Server’s authentication mode to Windows Authentication only, then restarting SQL Server—you’ll disable the sa account and you’ll have no other Windows Authenticated login defined in your SQL Server. You're locked out! To avoid this situation, implement the security measures in the correct order.

Do SQL Server 7.0 and SQL Server 6.x manage char values differently? If so, can I compensate for this difference by using the appropriate database compatibility level?

Using the 60 and 65 compatibility modes will solve most of the problems you might encounter in moving from SQL Server 6.x to 7.0. SQL Server 7.0 Books Online (BOL) contains more than 90 pages of information about compatibility issues. But BOL doesn’t help you manage char values. If you run the following script

DECLARE @test char (5)SELECT @test = 'a'SELECT right(@test, 1)

you will get different results in SQL Server 7.0 and SQL Server 6.5 regardless of the compatibility level that you use. SQL Server 2000 and 7.0 handles a char value as a char value, whereas SQL Server 6.x treats all nullable char values as varchar values.

How can I find out whether an object exists before I try to drop it?

You often want to test for a table's existence and delete it before recreating it. For example, the SQL Server 2000 Enterprise Manager scripting tool inserts the following code when it scripts a table:

IF EXISTS (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[authors]')ANDOBJECTPROPERTY(id, N'IsUserTable') = 1)DROP TABLE [dbo].[authors]

A quicker and more convenient way to do the same test is to more directly use the object_id() function:

USE pubsIF object_id('dbo.authors', 'U') IS NOT NULLDROP TABLE authorsGOCREATE TABLE authors...

The 'U' parameter in object_id() takes the same values as the xtype column in sysobjects for database objects such as tables, views, and stored procedures. But a problem arises when you use this technique with temporary tables because these tables exist in the tempdb database. Here's a way to extend the same technique to temporary tables but qualify the object name with tempdb as the database name:

USE pubsID object_id('tempdb.dbo.#MyTable', 'U') IS NOT NULLDROP TABLE #MyTableGOCREATE TABLE #MyTable...

How do I page the results from an SQL query from within SQL Server?

The example in Listing 4 demonstrates how to page results from a SQL query from within SQL Server. Note that this example uses the OpenQuery function, so you need to set the data access option on your local server (sp_serveroption) to true.

How do I get the current user’s effective permissions?

If you want to check whether the current user has a certain statement, object, or column permission, you can use the PERMISSIONS() function. The PERMISSIONS() function gives you the effective permissions that result from NT groups, server roles, and the user’s database role membership as well as explicit permissions granted to that user.

You can invoke the PERMISSIONS() function with no arguments to check statement permissions, with an object ID to check object permissions, or with both an object ID and a column name to check column permissions. The function returns an integer with a bitmap that contains the effective permissions. SQL Server Books Online (BOL) provides a detailed explanation about how to use this function and which bits represent which permissions. Listing 5 contains a few examples that demonstrate how to use the PERMISSIONS() function.

How can I get high-level information about a database schema?

For all SQL Server releases, you use the ODBC catalog stored procedures, the OLE DB provider stored procedures, and the system stored procedures to obtain information about the database schema. If you use these procedures, you’ll avoid writing code that relies on internal system tables and undocumented details.

Some of the relevant ODBC catalog stored procedures are:

A few examples of system stored procedures are:

You can use the OLE DB provider stored procedures on remote data sources defined in SQL Server. Some of the relevant stored procedures are:

SQL Server 2000 and 7.0 also provide the ANSI-SQL-compatible INFORMATION_SCHEMA views, which you can use to write powerful queries about the schema. Some of the ANSI-compatible views are