2006/08/30

Grant VIEW DEFINITION permission to tables and/or views

SQL Server 2005 still supports all of the fixed server and database roles introduced in SQL Server 2000, along with their default permissions. However, custom database roles, application roles, as well as server logins and database users can be granted (or denied) more granular permissions on a much wider range of objects. Some of the most commonly used permission types present in earlier version of SQL Server, such as EXECUTE, SELECT, or TAKE OWNERSHIP offer new possibilities in their new implementation (since they can be applied on different levels and offer inheritance capabilities). There are also others, newly introduced ones, including, for example:

  • CONTROL - functionally equivalent to all permissions granted to the object's owner and inherited by all subentities within its scope,
  • ALTER - provides the ability to alter properties of an object. Depending on the scope, you can limit its inheritance to objects of a specific type (for example, its variation in the form ALTER ANY 'object_type' grants permissions to modify every instance of 'object_type' within server or database scope). ALTER TRACE allows running Profiler without membership in SysAdmin fixed server role.
  • IMPERSONATE - as mentioned earlier, permits impersonating another user (without requiring SysAdmin or dbo privileges, as was the case in SQL Server 2000),
  • and VIEW DEFINITION

According to BOL about VIEW DEFINITION Permission:

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

And now the reasons for this blog entry: If you are using MS Access as your front end for SQL Server 2005, and you have tables with not-nullable columns with default values, as for instance:

CREATE TABLE [dbo].[MyTable] (
  [Id] [int] NOT NULL,
  ... more columns...
  [ColumnName] [int] NOT NULL CONSTRAINT [DF_TMyTable_ColumnName] DEFAULT ((0))
  CONSTRAINT [PK_TMyTable] PRIMARY KEY NONCLUSTERED
  ( [Id] ASC )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

your MS Access application will not be aware of the default values for columns in the table and your insertions will fail unless the client manually sets a value for those default values. According, to BOL on Viewing Object Definitions, this is by design:

If the user is not the table owner of myTable and has been granted only SELECT permission on the table, when the user accesses the following catalog views, the definition columns are NULL.
Catalog view name Transact-SQL definitions for
sys.sql_modules All triggers in myTable
sys.computed_columns All computed column in myTable
sys.check_constraints All CHECK constraints in myTable
sys.default_constraints All DEFAULT constraints in myTable

If you, your application or your users were used to work with SQL Server 2000 this will appear as a new behaviour, since granting SELECT access to a table in SQL Server 2000 also granted to read the structure of the table itself. Now, in SQL Server 2005, this is a different right and has to be granted explicitly. In fact it is the least privilege: you can only read the structure of the object (securable) but nothing else, you cannot even read data (SELECT). To solve the problem with MS Access not inserting the default values for tables and views, you simply have to grant users the right to VIEW DEFINITION of the tables/views. Keywords: SQL Server 2005, identity, not null, not nullable, default, default value, constraint, view definition, ms access, problem, issue, insert, select, permission.

2006/08/28

Offline files errors during synchronization

If you ever find the following error message when synching your Offline Files (Client Side Caching - CSC):
Unable to merge offline changes on \\server_name\share_name. The parameter is incorrect.
Or any of the following:
Using last known good configuration
File not found
More Details Available

You can apply the following procedure: How to re-initialize the offline files cache and database

Links: How to re-initialize the offline files cache and database Clint's post on Debugging Problems With Offline Files dated Tuesday, November 08, 2005 8:56 PM.

More errors: But there are other things that can happen (and really happen after all) sometimes. You might also get an Access is Denied error and it is not always because of wrong permissions. If you are sure that your NTFS permissions are correctly configured (because it works perfectly for other users, for instance) and still get the access denied error, you can try this:

  1. Disable the NIC
  2. Create an new file (anything will do) in the directory you are trying to synchronise
  3. Re-enable the NIC
  4. Synchronise
More links: Anonymous comments on Power Up Offline Files dated Tuesday - 1/13/04 says:
I know it is easy to complain but administrators need less fluff more substance. Your article gives a 5000 foot view. I can get all the 5000 foot view information from MS documentation... Offline files is great feature not just for mobile users but as a way to have “My Documents” be available to users when network connectivity is down and have them on a central file server for easy backup. Unfortunately, it either works or it does not. There is no middle ground and virtually nothing in the way of troubleshooting. For instance, it is not uncommon to receive an "Access is Denied" error when first configured. MS states that it is a permissions issue. However, this is not always the case. In a recent migration of 2000 users, we ran into this error 5% of the time. The fix is to disable the NIC, create a new file, enable the NIC and synchronize. Why? I do not know but it fixes it. This is the type of information that is truly useful.
Simply the kind of things that NOBODY would ever think about, but DO solve the problem.

2006/08/25

Using TOP 100 PERCENT in a view (SQL 2000 vs 2005 differences)

I'm still in the middle of the process of migrating our SQL servers 2000 to 2005. By now, all I have is a SQL 2005 publisher, but the subscriber is still a 2000 version. I run into problems when I started publishing all my views because of different syntax between 2000 and 2005 versions regarding TOP clause.

In SQL Server 2000 version, the syntax is TOP n [PERCENT] while in SQL Server 2005 the syntax is TOP (expression) [PERCENT]. Please note the parenthesis.

Those parenthesis are the reason for the publications to fail when the initial snapshot is sent to the subscriber (2000 version). I have been trying to find a way for SQL 2005 for not generating the extra parenthesis, but if you use Management Studio and the query designer to create the view, it seems there is no way to get rid of them. I tried also setting the database compatibility mode to 80 (2000) but it makes no difference. It does not matter whether the database mode is 90 (2005) or 80 (2000), the extra parenthesis are still there, making it impossible to publish a view that has an ORDER BY clause (for instance) for SQL Server 2000 subscribers.

Solution: I had to remove all TOP (n) PERCENT clauses and ORDER BY clauses to be able to publish my views correctly and modify my application accordingly. Of course, if you do not mind having to manually handle with T-SQL instead of using the visual query designer, you could still create your views with 2000 syntax (without the parenthesis) which is still valid for 2005, but in my case it did not worth the extra job and decided to keep on using the query designer and remove the TOP clauses.

Just for your information, I found this document SQL 2000 v. 2005 - Using Top 100 Percent in a View that throw me some light about the drawbacks of having TOP clauses in a view:

The obvious Pro is simplicity in access. While adding the ORDER BY to the query against the view really isn't all that difficult, it does make it a bit easier for quick/simple query access. BUT - there's a HUGE con here too. If the view starts getting used for other purposes (like in joins to other tables), then the being ordered before the joins, etc. can cause you an additional step that is NOT necessary. As a result, performance was compromised.

Because of those performance problems, I decided to solve my problem by the tough way: removing them completely. Now I have to use the ORDER BY clauses elsewhere (the client application), but not in the views.

2006/08/24

Account Lockout and Management Tools (Additional Account Info)

This one is quite old and I used to have it already installed on my servers, but now we are moving them to new hardware and I needed to google it to find it again. Account Lockout and Management Tools If you did not know it already, this is a suite of (free) tools, released by Microsoft, that can help you troubleshoot password and account information for your Active Directory users. One of the gems of this suit is AcctInfo.dll that installs an extra tab in Active Directory Users and Computers snap-in. This new tab (called Additional Account Info) shows you:
  • Password last set (date & time)
  • Password expires (date & time)
  • Locked (Yes / No)
  • Last-Logon-Timestamp (date & time)
  • SID
  • GUID
  • Last Logon (date & time)
  • Last Logoff (date & time)
  • Last Bad Logon (date & time)
  • Logon Count (integer)
  • Bad Password Count (integer)
Extracted from Microsoft documentation:
ALTools.exe includes:
  • AcctInfo.dll. Helps isolate and troubleshoot account lockouts and to change a user's password on a domain controller in that user's site. It works by adding new property pages to user objects in the Active Directory Users and Computers Microsoft Management Console (MMC).
  • ALockout.dll. On the client computer, helps determine a process or application that is sending wrong credentials.Caution: Do not use this tool on servers that host network applications or services. Also, you should not use ALockout.dll on Exchange servers, because it may prevent the Exchange store from starting.
  • ALoInfo.exe. Displays all user account names and the age of their passwords.
  • EnableKerbLog.vbs. Used as a startup script, allows Kerberos to log on to all your clients that run Windows 2000 and later.EventCombMT.exe. Gathers specific events from event logs of several different machines to one central location.
  • LockoutStatus.exe. Determines all the domain controllers that are involved in a lockout of a user in order to assist in gathering the logs. LockoutStatus.exe uses the NLParse.exe tool to parse Netlogon logs for specific Netlogon return status codes. It directs the output to a comma-separated value (.csv) file that you can sort further, if needed.
  • NLParse.exe. Used to extract and display desired entries from the Netlogon log files.
In order to install 'Additional Account Info':
  • Copy acctinfo.dll to the %systemroot%\system32 folder
  • Register the service and dll running regsvr32 acctinfo.dll
  • Close, then open Active Directory Users and Computers.
NOTE 1: If you are using Windows 2003, you should use acctinfo.dll provided with Windows 2003 Resource Kit Tools instead. NOTE 2: If you are using x64 versions of Windows, please note that Windows 2003 Resource Kit Tools are not supported on 64-bit platforms.

2006/08/05

VSS & ntbackup errors

We run our backups through scheduled tasks with ntbackup. We do a SystemState backup every day (for us it is enough, maybe in other scenarios more frecuent backups are needed). As part of our dayly checks, we check last run status in the Scheduled Tasks for every server to see if it was successfull (error code 0). Some weeks ago, in order to verify that the backups were restorable, we went to see the actual file (.bkf) and, for our surprise, it was only 2Kb in size. Fortunately, we did not really need to restore the server, it was running fine but... not so fine if the backups were not being done. Why did not we notice it before? Scheduled Task last status said: 0 Ok We went to check the Application Event Log and found:
Type: Error
Source: NTBackup
Category: None
Event ID: 8019
Date: 7/17/2006
Time: 10:16:03 PM
User: N/A
Computer: SERVERNAME
Description: End Operation: Warnings or errors were encountered.
Consult the backup report for more details.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
So we went to check the ntbackup log file, located in %USERPROFILE$\Local settings\Application data\Microsoft\Windows NT\NTBackup\data, and found:
Backup Status
Operation: Backup
Active backup destination: File
Media name: "System State SERVERNAME.bkf created 2006-07-17 at 22:15"

Volume shadow copy creation: Attempt 1.
"MSDEWriter" has reported an error 0x800423f4. This is part of System State.
The backup cannot continue.

Error returned while creating the volume shadow copy: 800423f4
Aborting Backup.

----------------------

The operation did not successfully complete.

----------------------
In spanish:
Estado de la copia de seguridad
Operación: copia de seguridad
Destino de la copia de seguridad activo: Archivo
Nombre del medio: "System State MYSERVER.bkf creado 17/07/2006 a las 22:15"

Creación de instantánea de volumen: Intento 1.
"MSDEWriter" informó acerca de un error 0x800423f4. Esto forma parte del estado del sistema.
La copia de seguridad no puede continuar.

Error devuelto al crear la instantánea de volumen: 800423f4
Anular la copia de seguridad.

----------------------

Operación cancelada.

----------------------
After some research we found that we were experiencing the symptoms exposed in KB828481 - Error 800423f4 appears in the backup log file when you back up a volume by using the Volume Shadow Copy service in Windows Server 2003 with some particularities: Our server is a Windows 2003 Server R2 Standard x64 running as domain controller and with SQL Server 2005 Standard + SP1 installed. Some of our SQL databases have a recovery mode set as Full and should be kept in this mode (changing them into Simple is not an option). However, if we check the server file versions and timestamps for them, and compare them with the data shown in the former KB828481 we found that our server's are more up to date (newer) than those shown in the KB828481:
ntbackup.exe  5.2.3790.1830 (srv03_sp1_rtm.050324-1447)   30/Nov/2005 14:00 
ws03res.dll   5.2.3790.1830 (srv03_sp1_rtm.050324-1447)   30/Nov/2005 14:00 
Wws03res.dll  Not found in our server
Being the files in our server newer than those exposed in KB828481, we understand that the hotfix described there is not suitable for our case, even though we are experiencing the behaviour shown there. After a little more research we found KB913648 - A new Volume Shadow Copy Service update is now available that fixes various Volume Shadow Copy Service problems in Windows Server 2003. In this case the document is newer (Juy 26, 2006) but having a look into the problems fixed by this hotfix suggests us that this is not our case either (too complex things for our little simple configuration/server). Besides, the server had been doing the backups for months (confirmed) before we noticed that it was not doing them anymore. Just for your information, when we run vssadmin list writers in a command prompt, the process seemed to wait indefinitely and, after 30 minutes, we press CTRL+C:
C:\WINDOWS\system32>vssadmin list writers
vssadmin 1.1 - Volume Shadow Copy Service administrative command-line tool
(C) Copyright 2001 Microsoft Corp.

Waiting for responses.
These may be delayed if a shadow copy is being prepared.

^C
C:\WINDOWS\system32>
The server experiencing the problem has not a very high workload, not from the fileserver point of view (litle company with 25 employees), nor from the SQL Server's transactional point of view (somewhat near 1000 transactions per hour). Solution: Finally, the steps that solved the problem were:
cd %windir%/system32
net stop vss
regsvr32 /s ole32.dll
regsvr32 /s vss_ps.dll
vssvc /Register
regsvr32 /s /i swprv.dll
regsvr32 /s /i eventcls.dll
regsvr32 /s es.dll
regsvr32 /s stdprov.dll
regsvr32 /s vssui.dll
regsvr32 /s msxml.dll
regsvr32 /s msxml3.dll
regsvr32 /s msxml4.dll
And a reboot of the server. Links:

2006/08/03

ISA Server 2004: fwsrv stopped responding to all requests

We recently installed ISA Server 2004 Service Pack 2 and then applied the recommended update (point 3 is KB897716) Microsoft Internet Security and Acceleration (ISA) Server 2004 Standard Edition RPC Filter Blocks Outlook Traffic from Computers Running Windows Server 2003 Service Pack 1 (SP1) After doing it we did not have to restart the server and everything seemed to be working perfectly. I also read about BITS Caching (Background Intelligent Transfer Service, used by windowsupdate) being supported and decided to use it (you can read about how to do it searching for Creating the Microsoft Update Cache Rule in Planning, Deployment, and Integration for ISA Server 2004 Service Pack 2). Some days later, due to other reasons, we had to restart the server. After that, the Firewall Service failed to start, thus leaving us disconnected from the internet. All netwok traffic (VPNs, http, email) was blocked. As you can imagine, we were into real trouble. In our application event log there were 2 kind of error messages:
Event Type: Error
Event Source: Microsoft ISA Server Control
Event Category: None
Event ID: 14079
Date:  8/2/2006
Time:  12:30:01 PM
User: N/A
Computer: SERVERNAME
Description:
Due to an unexpected error, the service fwsrv stopped responding to all
requests. Stop the service or the corresponding process if it does not
respond, and then start it again. Check the Windows event Viewer for
related error messages.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Event Type: Error
Event Source: Microsoft ISA Server
Event Category: None
Event ID: 1000
Date:  8/2/2006
Time:  12:28:55 PM
User: N/A
Computer: SERVERNAME
Description:
Faulting application wspsrv.exe, version 4.0.2165.610, stamp 442d48f1,
faulting module w3filter.dll, version 4.0.2165.610, stamp 442d48dd,
debug? 0, fault address 00094cff.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
In the system event log we had:
Event Type: Error
Event Source: Service Control Manager
Event Category: None
Event ID: 7034
Date: 8/2/2006
Time: 12:29:08 PM
User: N/A
Computer: SERVERNAME
Description:
The Microsoft Firewall service terminated unexpectedly. It has done this 6
time(s).

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
With those clues, we found The Firewall service stops responding and Event IDs 14079, 1000, and 14057 are logged in the Application event log in ISA Server 2004 that suggested installing Update for HTTP issues in Internet Security and Acceleration Server 2004 Service Pack 2. We did it but it did not solve the problem either. One of the symptoms in the latter document was:
917134 The "Background Intelligent Transfer Service" option is incorrectly available for any non-Microsoft Update cache rule that you create in ISA Server 2004
I revised the cache rules that I had configured, but the only one that had the BITS Cache enabled was the one created by the wizard. None of the rest had that option enabled. Just in case, I deleted the Microsoft Update Cache Rule that I had created some days earlier. No luck either: the service kept on stopping. And then I had another idea: since all the clues guided me towards the cache... why not to disable the cache completely, delete c:\urlcache\Dir1.cdat file manually, and then re-enable the cache again? If the problem was the data that ISA had already saved in the cache, that would be the only way to get rid of it. The idea was quite simple and seemed risk-free. We had not read a word about this self-made procedure but after doing it, the Firewall service restarted without any problem. Summing up:
  1. Install ISA Server 2004 SP2.
  2. Install KB897716.
  3. Install KB916106.
  4. Do NOT add any cache rule with BITS Caching enabled (just in case).
If you run into the same problems as I did:
  1. Remove any of the rules that had BITS Caching enabled.
  2. Disable caching for all drives.
  3. Manually delete the cache files.
  4. Re-enable the caching for the drives you had previously configured.
Links: