Re: sp_revoke login is not working as expected.

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







.

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • 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)