Re: sp_revoke login is not working as expected.
- From: "Dan Guzman" <guzmanda@xxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Aug 2008 19:38:05 -0500
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.
--------------------------------------------------------------------------
.
- Follow-Ups:
- 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: how to find out if a domain user is in the sysadmin server role?
- Next by Date: Re: SQL Server 2005 Windows Authentication - logon failure
- Previous by thread: Re: how to find out if a domain user is in the sysadmin server role?
- Next by thread: Re: sp_revoke login is not working as expected.
- Index(es):
Relevant Pages
|
