October 31, 2005 at 3:43 am
I know this looks as though it is a security Issue but have exhausted the usual posibilities &
thought I would post it here aswell as the security forum.
The situation is as follows:
we have a user database where all the users are added through a windows group.
I was checking the database the other day & found that someone had added a user to the database rather than the group.
I deleted this user from the database and added them to the windows group.
All is well I thought.. but no the DEV who was looking after the Application said that when he tried to connect the user to the database uswing windows Auth then they were getting the error
"Cannot open User Default Database. Login failed".
Dropped the user & login, recreated them & the same problem..
Changed the User default database & same problem
Ran sp_DBHELP
============================
Database LDF_Consult
db_size 277.69 MB
Owner HORSHAM1\IT_BRADLE1
dbid 62
created Feb 11 2005
Status Status=ONLINE,
Updateability=READ_WRITE,
UserAccess=MULTI_USER,
Recovery=FULL,
Version=539,
Collation=Latin1_General_CI_AS,
SQLSortOrder=0,
IsTornPageDetectionEnabled,
IsAutoCreateStatistics,
IsAutoUpdateStatistics
Compatability level 80
======================
All looks fine
I have also run the following script on the database (found it on this site)
select cast(object_name([id]) as varchar(40)) [object],
(CASE [action]
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
END) [action],
(CASE protecttype
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
END) protecttype,
columns --only needed in master and reference was altered for better view below.
from sysprotects where uid = 0 order by object
With the following results
object action protecttype columns
---------------------------------------- ---------------- ------------- --------
NULL NULL GRANT NULL
CommentXDoc SELECT GRANT 0x01
Consultee SELECT GRANT 0x01
ContributorsComments SELECT GRANT 0x01
ExternalAddress SELECT GRANT 0x01
LiveGazView SELECT GRANT 0x01
syscolumns SELECT GRANT 0x01
syscomments SELECT GRANT 0x01
sysconstraints SELECT GRANT 0x01
sysdepends SELECT GRANT 0x01
sysfilegroups SELECT GRANT 0x01
sysfiles SELECT GRANT 0x01
sysforeignkeys SELECT GRANT 0x01
sysfulltextcatalogs SELECT GRANT 0x01
sysindexes SELECT GRANT 0x01
sysindexkeys SELECT GRANT 0x01
sysmembers SELECT GRANT 0x01
sysobjects SELECT GRANT 0x01
syspermissions SELECT GRANT 0x01
sysprotects SELECT GRANT 0x01
sysreferences SELECT GRANT 0x01
syssegments SELECT GRANT 0x01
systypes SELECT GRANT 0x01
sysusers SELECT GRANT 0x01
I am now at a loss as to the cause of the problem
Any help to resolve the issue would be greatly appreciated.
Regards
Andy
October 31, 2005 at 6:41 am
You deleted the user from the database, but not the login from the server? Just a guess.
If the login is still there, they're connecting through it, not through the NT/AD group.
Grant
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 31, 2005 at 9:18 am
Here was another post recently that said that if a user is a member of more then one Windows groups that his default database may conflict.
The solution suggested here to create a dummy database or use Tempdb to set all users and groups have as their default database and then specify a database explicitly in a connection string or ODBC source.
The possible problem with this approach that some third-party programs and reports do not specify the production database and tell you to have a user having a default database point to production.
Regards,Yelena Varsha
November 1, 2005 at 5:58 am
I was going to suggest the same as Yelena. If the user is a member of more than 1 group, and each group has a different default database, then you can get a conflict in default database assignment during the SQL connection.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 1, 2005 at 9:40 am
What is the user default database? Is this database currently online or was deleted some years ago.
If the default database no longer exist, you need to change the user default db to something else otherwise try
--Change the user account to link with the 'NewMary' login.
USE pubs
go
EXEC sp_change_users_login 'Update_One', 'Mary', 'NewMary'
mom
November 29, 2005 at 2:35 am
This happened to me after I cancelled a DBCC SHRINKFILE command. I was shrinking a database that was taking too long. After I cancelled the DBCC SHRINKFILE command, I was logged off. When I tried to logon again, then I got this strange error. I was able to logon using a SQL account but not to my domain account. I found the answer when I read this post. I logged on as SA and updated the security for the domain group where I belong and added a default database. The strange error disappeared.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply