March 5, 2010 at 3:20 pm
Hi,
Is there any way that when creating a new SQL login and creating the user for that login that it would get disabled? I created a login and user via TSQL a few days ago, and just discovered the user accounts were disabled, with the little red arrow on the user icon. I created another user the same way that is not disabled. They are Windows Domain accounts. Anybody seen this before?
Thanks,
Jason
March 5, 2010 at 5:18 pm
Could you share the T-SQL Script that you used?
Are the logins diabled as well as the user accounts?
How long after you created the accounts did you notice that they were disabled?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 5:27 pm
Certainly. I started out creating the login:
CREATE LOGIN [DOMAIN\UserGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[MyDB]
GO
There are 8 other groups being added to LOGINS this way. I'm updating multiple DBs, so this is from the DB affected:
USE [MyDB]
GO
CREATE USER [DOMAIN\UserGroup] FOR LOGIN [DOMAIN\UserGroup]
GO
EXEC sp_addrolemember N'db_datareader', N'DOMAIN\UserGroup
GO
Basically I just copied the script of creating a different login/user from the GUI and pasted that into my script, editing where needed. The login created successfully, and the user created, however it has the down-pointing red arrow on its icon. I've got other users created successfully with this same method, so I'm not sure what went awry. I ran this script about 8 days ago, and have been looking at why the groups didn't work, just found the disabled user today. Thanks for looking.
March 5, 2010 at 5:35 pm
You can disable a user account via a Group policy.
Anyone who is a local admin, domain admin or enterprise admin inherits the SQL Server Fixed Server Role.
If you run a variation of your script does the user account become disabled immediately?
You may want to consider auditing...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2010 at 5:44 pm
I haven't tried that yet, just hesitant to try it again. However I did manually try to add the same group via the GUI, and it completed successfully, with the disabled arrow gone. I had a feeling it would do that, but I'm curious to find out why it happened in the first place.
March 5, 2010 at 6:30 pm
I suggest that you reproduce the problem.
You can run a variation of the script that you initially ran.
Going through the GUI will not assist you in reproducing the problem.
Then I would suggest that you perform auditing. Otherwise who who knows what happened...
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 8, 2010 at 4:10 pm
jpoe-838861 (3/5/2010)
Hi,Is there any way that when creating a new SQL login and creating the user for that login that it would get disabled? I created a login and user via TSQL a few days ago, and just discovered the user accounts were disabled, with the little red arrow on the user icon. I created another user the same way that is not disabled. They are Windows Domain accounts. Anybody seen this before?
Thanks,
Jason
Did you move users from one server to another server? or creating new users?
EbjoY!
March 8, 2010 at 4:16 pm
Welsh, I found my issue. Really a bonehead mistake, but a problem nonetheless...
When I created the user on the db, I had a typo in there:
CREATE USER [DOMAIN\AGroup] FOR LOGIN [DOMAIN\BGroup]...
Missed one part when doing the copy/paste. That is what is causing the user to disable. However, I try to run that same script on another test server and the user does NOT get disabled, no red arrow on the user icon. I do receive the error 'DOMAIN\AGroup' is not a valid name because it contains invalid characters. I do remember seeing that when I ran the script initally, but I thought I had fixed it...
GT, I am trying to add completely new users.
I think I am good to go here, but would still like to know why it would disable on one server and not the other. I've tried to run an audit trace, but it doesn't look like anything out of the ordinary is happening, at least from what I can tell.
Thanks all for your help.
//EDIT
It wasn't really the mismatching group, that part died with the error message, it was the next line:
EXEC sp_addrolemember N'db_datareader', N'DOMAIN\AGroup'
It was trying to add a role to a group that didn't exist, and it did add an entry but it was disabled.
March 8, 2010 at 7:12 pm
Well that it is awesome that you have made progress. 🙂
I was not sure if you needed anything from me but if so, please give me a shout.
Thanks,
W.C.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply