November 29, 2010 at 2:38 am
I have an unusual problem.
I am connected to my production SQL Server and have just added a new trusted login.
This has been done successfully, however I need to disable the login as it is only to be enabled on an "as required" basis for special conditions.
However when i use either the management studio or the ALTER LOGIN [login] DISABLE command, i get back the error message:
Disable Login. failed for Login 'xxx\xxxx'
Cannot alter the login 'xxx\xxxx', because it does not exist or you do not have permission.
I have double checked the following:
I am definitely connected as a system administrator.
I have the necessary permissions to alter logins.
I can delete the login.
I can map and unmap the login to database and grant/deny/revoke permissions on objects
I can connect to the database using the login and query tables, etc., as appropriate to the way I have set it up.
But I cannot change the enabled or disabled status of the login.
Can anyone help?
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
November 29, 2010 at 11:01 am
Quick check, does this return any rows?
select name, type_desc, is_disabled
from sys.server_principals
where name = 'xx\xxxx'
December 1, 2010 at 1:46 am
Yes, one row
type_desc = WINDOWS_GROUP
is_disabled = 0
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
December 1, 2010 at 2:19 am
cannot disable from the GUI?
December 1, 2010 at 2:29 am
If I try to disable the login from either the GUI or direct using T-SQL both come back with the same error.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
December 1, 2010 at 6:15 pm
You cannot disable windows groups.
From BOL (TSQL Reference on ALTER LOGIN):
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:
"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."
This is by design.
December 2, 2010 at 12:58 am
Argh!
Missed this one, thanks.
This is going to make administering the logins a pain, lol. :hehe:
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
December 2, 2010 at 6:47 pm
You may be able to rig something with a login trigger.
October 7, 2013 at 11:59 am
gklundt (12/1/2010)
You cannot disable windows groups.From BOL (TSQL Reference on ALTER LOGIN):
You cannot use ALTER_LOGIN with the DISABLE argument to deny access to a Windows group. For example, ALTER_LOGIN [domain\group] DISABLE will return the following error message:
"Msg 15151, Level 16, State 1, Line 1
"Cannot alter the login 'Domain\Group', because it does not exist or you do not have permission."
This is by design.
October 7, 2013 at 1:40 pm
You should still be able to DENY CONNECT to that Group...
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
October 7, 2013 at 1:50 pm
Please note: 3 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2013 at 1:57 pm
*lol*
:blink:
...
(wasn't me... :ermm:)
Andreas
---------------------------------------------------
MVP SQL Server
Microsoft Certified Master SQL Server 2008
Microsoft Certified Solutions Master Data Platform, SQL Server 2012
www.insidesql.org/blogs/andreaswolter
www.andreas-wolter.com
October 10, 2013 at 12:34 pm
FYI : If you use DENY then whoever are into that group lost SQL Server level access for that particular SQL Server even though they are in some other group which has DB reader etc.. permission on some database on same server . and You end up with error 18456....
October 10, 2013 at 12:35 pm
FYI : If you use DENY then whoever are into that group lost SQL Server level access for that particular SQL Server even though they are in some other group which has DB reader etc.. permission on some database on same server . and You end up with error 18456....
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply