I have encountered issues after adding logins to the SQL Server 2000 databases where even though the servers show up in the user list they still cannot access the database. It turns out I only granted the user permissions to the database and not actually added them; so it failed. In doing that found following states:
| State | Description |
|
4 |
User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT Group). |
|
6 |
User has permissions to the database (objects) and user belong to the database (Windows NT Group). |
|
12 |
User has permissions to the database (objects) but user doesn’t belong to the database (Windows NT User). |
|
14 |
User has permissions to the database (objects) and user belong to the database (Windows NT User). |
I wouldn’t recommend making any coding on this; but it is a good reference. And I don’t know if these hold true for SQL Server 2005 or 2008 yet.
SQL Canada (SQLCAN) 