Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sysadmin cannot alter login Expand / Collapse
Author
Message
Posted Monday, November 29, 2010 2:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:49 AM
Points: 589, Visits: 404
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.
Post #1027195
Posted Monday, November 29, 2010 11:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 7, 2014 1:11 PM
Points: 26, Visits: 121
Quick check, does this return any rows?
select name, type_desc, is_disabled 
from sys.server_principals
where name = 'xx\xxxx'

Post #1027477
Posted Wednesday, December 1, 2010 1:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:49 AM
Points: 589, Visits: 404
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.
Post #1028461
Posted Wednesday, December 1, 2010 2:19 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 4:32 AM
Points: 562, Visits: 1,036
cannot disable from the GUI?
Post #1028468
Posted Wednesday, December 1, 2010 2:29 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:49 AM
Points: 589, Visits: 404
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.
Post #1028478
Posted Wednesday, December 1, 2010 6:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 7, 2014 1:11 PM
Points: 26, Visits: 121
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.
Post #1029037
Posted Thursday, December 2, 2010 12:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, December 1, 2014 4:49 AM
Points: 589, Visits: 404
Argh!
Missed this one, thanks.
This is going to make administering the logins a pain, lol.



---------------------------------------
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.
Post #1029097
Posted Thursday, December 2, 2010 6:47 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 7, 2014 1:11 PM
Points: 26, Visits: 121
You may be able to rig something with a login trigger.
Post #1029606
Posted Monday, October 7, 2013 11:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 6:12 PM
Points: 827, Visits: 342
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.


http://msdn.microsoft.com/en-us/library/ms189828.aspx
Post #1502287
Posted Monday, October 7, 2013 1:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:24 AM
Points: 153, Visits: 981
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
Post #1502340
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse