| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2008-08-21 : 11:51:48
|
| 1. I have a domain\user having sql access. 2. This user is also a member of domain\group Since the user a member of group, I revoked the domain\user access by Sp_revokelogin ‘domain\user’After doing this the user is NOT able to access sql server. I was expecting to have access, because the DOMAIN\GROUP already access. Am I missing anything?------------------------I think, therefore I am - Rene Descartes |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2008-08-21 : 12:50:27
|
| Which applies to every form of permissions I have encountered from unix to windows to file systems to spam filters to huntgroups on phone systems |
 |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 12:52:59
|
| what is the solution for my case?------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 12:59:49
|
| 1. I have many users, for whom access has been given for individual domain accounts2. All of them are also a part of group which has required access3. I want to remove the individual domain logins (because a group is already in place), so that things will be bit neat ------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 13:17:41
|
quote:
Originally posted by tkizer
Just remove the logins, don't revoke access.
In SQL2K5 I used DROP LOGIN. How to do that in SQL2K? (When I tried to delete the login in EM, i found, it is using sp_revokelogin)------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-21 : 13:37:40
|
| I have to admit that I didn't read your initial thread word for word. With as many posts as we get here, I often skim them and miss important parts. The important part of your thread is that you ran Sp_revokelogin ‘domain\user’ and now the user can't connect. I thought you had denied access. I'll need to think about this one.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-21 : 13:39:31
|
Here is the key part from SQL Server 2000 Books Online:quote:
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.
You are seeing the opposite. Has the user tried logging out of his computer and then back in or tried relaunching the SQL client tool?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 13:47:35
|
| Looks like Sp_revokelogin is behaving diffrently in SQL2K and in SQL2K5. I will test both and post my findings. Thank you tara.------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-21 : 13:50:04
|
| Is your question about SQL Server 2000 or 2005? DROP LOGIN is what you use in SQL Server 2005. sp_revokelogin is deprecated.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 13:57:00
|
| I need to do this both on SQL2K and SQL2K5. To summerise, SQL2K5 -> DROP LOGIN SQL2K -> sp_revokelogin ------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 14:21:40
|
| Thank you Tara. Everything is clear now. I have one more question. Is there a way to DISABLE , SQL LOGINS in SQL2K. I have posted the question here, http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109245------------------------I think, therefore I am - Rene Descartes |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
Posted - 2008-08-21 : 14:27:27
|
| I am thinkng of just changing the password! If someone raises alarm, I can revert the password.------------------------I think, therefore I am - Rene Descartes |
|
|
|
|
SQL login functionality