Re: sp_revoke login is not working as expected.

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



.

Relevant Pages

  • Re: sp_revoke login is not working as expected.
    ... when I use DROP LOGIN and subsequently try to connect i get the folloiwng ... Since sysadmin role members are mapped to the dbo user in all databases, ... SQL Server MVP ... All of them are also a part of a windows group which has required ...
    (microsoft.public.sqlserver.security)
  • RE: W2K Professional Install with original CD (pre-service packs)...a
    ... install up, then when I rebooted it was time for the critical patch to .NET ... only valid club members can have that. ... old thing was this Windows 2000 Professional CD that never existed before the ... svchost.exe trying to access the Internet. ...
    (microsoft.public.win2000.setup)
  • Re: Distribution list update
    ... OL2000 is on Windows XP. ... >>> members, just not the members of the distribution lists. ... >>> different client ...
    (microsoft.public.exchange.clients)
  • Re: Creating a C++ like message loop in .NET threaded classes.
    ... Just grab your good ol' C++ message loop knowledge and write the same code in .NET, calling API functions such as GetMessage and DispatchMessage through P/Invoke. ... control over Windows message processing (or maybe it's in the .NET and I ... Now I don't necessarily want the code of these members ... the object processes the message when its thread is ...
    (microsoft.public.dotnet.framework)
  • Re: users last-logon-timestamp
    ... Otherwise memberOf will only match the direct members. ... Co-author of "The .NET Developer's Guide to Directory Services Programming" ... Also, lastLogonTimestamp is a "normal" date time, so you don't need to ... we are at windows 2000 native mode with mixed Windows ...
    (microsoft.public.windows.server.active_directory)