Re: sp_revoke login is not working as expected.
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 26 Aug 2008 06:51:13 -0500
I tried,
EXEC xp_logininfo 'MyDomain\SomeUserAccount','all'
as well as
EXEC xp_logininfo 'MyDomain\SomeUserAccount','members'
the command lists the member users.
Try specifying a group member rather than the group. This should list the Windows groups the user can connect with.
-----------------------------------------------------------
can not open user default database. Login failed
-----------------------------------------------------------
This is a general message; You will get the same message when you try to
connect with the non-existing login.
I don't get this error on my server when the login doesn't exist. If I try to connect with a Windows account that does not have a login, I get only "login failed for user domain\user" (error number: 18456, Severity: 14, State: 1).
What is the default database of the Windows group? Is that database accessable?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Ravi Lobo" <RaviLobo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:674C82FF-064E-486A-87D0-D422DF9C7DC1@xxxxxxxxxxxxxxxx
I tried,
EXEC xp_logininfo 'MyDomain\SomeUserAccount','all'
as well as
EXEC xp_logininfo 'MyDomain\SomeUserAccount','members'
the command lists the member users.
when I use DROP LOGIN and subsequently try to connect i get the folloiwng
error
-----------------------------------------------------------
can not open user default database. Login failed
-----------------------------------------------------------
This is a general message; You will get the same message when you try to
connect with the non-existing login.
Is there a work around?
"Dan Guzman" wrote:
> 1. Yes the windows group has access. (It is SYSADMIN)
Since sysadmin role members are mapped to the dbo user in all databases, you
don't need to explicitly grant database access in that case. I assumed a
non-sysadmin login in my initial response.
> 2. I first tried DROP LOGIN (even before sp_revokelogin), even that > didn't
> work as expected.
Both should provided the same functionality but sp_revokelogin is provided
only for backwards compatibility. Use CREATE/DROP for new development and
when you modify existing scripts.
Try running xp_logininfo for one on the group members to list all
permissions paths. Perhaps this will provide a clue as to what's going on.
For example:
EXEC xp_logininfo 'MyDomain\SomeUserAccount','all'
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Ravi Lobo" <RaviLobo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B7294359-7349-4766-9D5B-185D0F5AF628@xxxxxxxxxxxxxxxx
> 1. Yes the windows group has access. (It is SYSADMIN)
> 2. I first tried DROP LOGIN (even before sp_revokelogin), even that > didn't
> work as expected.
>
> "Dan Guzman" wrote:
>
>> Since you are on SQL 2005, you should use CREATE/DROP LOGIN instead of
>> sp_grantlogin/sp_revokelogin. Similarly, you should use CREATE/DROP >> USER
>> instead of sp_grantdbaccess/sp_revokedbaccess.
>>
>> sp_grantlogin/sp_revokelogin should do the job, though. I didn't see >> any
>> mention of sp_grantdbaccess or CREATE USER in your post. Does the
>> Windows
>> group have access to the database(s) in question?
>>
>> -- >> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>>
>> "Ravi Lobo" <RaviLobo@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:1618A330-7465-4634-B6B8-6A0D2F60BAD9@xxxxxxxxxxxxxxxx
>> > 1. I have many windows users, for whom access has been given for
>> > individual
>> > domain accounts (SQL2K5)
>> > 2. All of them are also a part of a windows group which has required
>> > access
>> > 3. I want to remove the individual domain logins (because a group is
>> > already
>> > in place), so that things will be bit neat
>> >
>> > I used sp_revokelogin for individual accounts , but after that my >> > users
>> > are
>> > not able to access sql server. BOL states the use of sp_revokesql as
>> > exactly
>> > I need however it doesn't work that way,
>> >
>> > -----------------------------------------------------------------------------
>> > sp_revokelogin does not explicitly prevent Windows NT users from
>> > connecting
>> > to SQL Server, but prevents Windows NT users from doing so through
>> > their
>> > Windows NT user accounts. However, Windows NT users can still >> > connect
>> > if
>> > they
>> > are members of a Windows NT group that has been granted access to >> > SQL
>> > Server
>> > using the sp_grantlogin stored procedure. For example, if Windows NT
>> > user
>> > REDMOND\john is a member of the Windows NT group REDMOND\Admins, and
>> > REDMOND\john is revoked access using:
>> >
>> > sp_revokelogin [REDMOND\john]
>> >
>> > REDMOND\john can still connect if REDMOND\Admins is granted access.
>> > Similarly, if REDMOND\Admins is revoked access but REDMOND\john is
>> > granted
>> > access, REDMOND\john can still connect.
>> > --------------------------------------------------------------------------
>> >
>> >
>> >
>>
>>
.
- References:
- Re: sp_revoke login is not working as expected.
- From: Dan Guzman
- Re: sp_revoke login is not working as expected.
- From: Ravi Lobo
- Re: sp_revoke login is not working as expected.
- From: Dan Guzman
- Re: sp_revoke login is not working as expected.
- From: Ravi Lobo
- Re: sp_revoke login is not working as expected.
- Prev by Date: Re: sp_revoke login is not working as expected.
- Next by Date: Obtain Client IP for Login Failures (SQL 2000)
- Previous by thread: Re: sp_revoke login is not working as expected.
- Next by thread: RE: Setuser failed?
- Index(es):
Relevant Pages
|
