Re: sp_revoke login is not working as expected.

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: Slow booting xp home.
    ... Changing the boot order to boot first from your hard disk might save you a half second, but you won't be able to boot from a CD until you change it back - and the time spent to do that will erase any previous time saved. ... 2- Consider what software you really want to start with Windows and also how you've configured your applications at startup ... Installing and Registering Visual Studio Express Editions Smart Device ...
    (microsoft.public.windowsxp.perform_maintain)
  • Re: login 101..
    ... On Windows 2003, SQL Server 2005 can enforce the Windows password complexity ... Windows authentication - SQL Server uses a special protocol to ask ... user is in the list of allowed logins, ...
    (microsoft.public.sqlserver.security)
  • RE: How to create a trusted connection
    ... You need to grant access for the Windows login by referring to the books ... is set to use Windows authentication to be able to do trusted connection. ... There are two modes of authentication in SQL Server: ...
    (microsoft.public.sqlserver.security)
  • Re: Windows NT user or group not found.
    ... out what SQL Server thinks the login name is vs. what Windows thinks the ... When the SQL Server collation and the Windows collation are different, ... The following example uses the Param2 parameter to get the SID from Windows ...
    (microsoft.public.sqlserver.connect)
  • RE: How to create a trusted connection
    ... What do you mean by "creating a Windows login"? ... To login SQL Server 2000 using trusted connection, grant the Windows ... Group" in SQL Server Books Online to get the concept, ...
    (microsoft.public.sqlserver.security)