sp_revoke login is not working as expected.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ravi Lobo - 21 Aug 2008 21:48 GMT

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.
--------------------------------------------------------------------------

Reply to this Message

Dan Guzman - 22 Aug 2008 01:38 GMT

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?

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> 1. I have many windows users, for whom access has been given for
> individual
[quoted text clipped - 29 lines]
> access, REDMOND\john can still connect.
> --------------------------------------------------------------------------

Reply to this Message

Ravi Lobo - 22 Aug 2008 14:29 GMT

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.

> 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
[quoted text clipped - 37 lines]
> > access, REDMOND\john can still connect.
> > --------------------------------------------------------------------------

Reply to this Message

Dan Guzman - 23 Aug 2008 13:00 GMT

> 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'

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

> 1. Yes the windows group has access. (It is SYSADMIN)
> 2. I first tried DROP LOGIN (even before sp_revokelogin), even that didn't
[quoted text clipped - 46 lines]
>> > access, REDMOND\john can still connect.
>> > --------------------------------------------------------------------------

Reply to this Message

Ravi Lobo - 25 Aug 2008 21:10 GMT

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?

> > 1. Yes the windows group has access. (It is SYSADMIN)
>
[quoted text clipped - 65 lines]
> >> > access, REDMOND\john can still connect.
> >> > --------------------------------------------------------------------------

Reply to this Message

Dan Guzman - 26 Aug 2008 12:51 GMT

>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?

Signature

Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

>I tried,
> EXEC xp_logininfo 'MyDomain\SomeUserAccount','all'
[quoted text clipped - 94 lines]
>> >> > access, REDMOND\john can still connect.
>> >> > --------------------------------------------------------------------------

Reply to this Message