Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

Enabling and disabling logins in SQL Server 2005

In SQL Server, much like other services with users, login accounts can be enabled or disabled. An enabled login can be authenticated and allowed access to database resources. A disabled login is not allowed to establish a connection to the SQL Server instance.

For example, let's create a login called JoeUser.

CREATE LOGIN JoeUser WITH 
PASSWORD = 'P@$$w0rd!'
,DEFAULT_DATABASE = AdventureWorks
USE AdventureWorks;

CREATE USER JoeUser

In the first statement, the JoeUser login was created with a fairly complex password and a default database of AdventureWorks. Afterward, we switch to the AdventureWorks database and create a new user for the database. If we attempt to login to SQL Server using these credentials, we can.

Next let's disable the login with the following statement.

ALTER LOGIN JoeUser DISABLE

If someone attempts to login to SQL Server using the JoeUser login, the connection attempt is rejected and Error 18470 is returned - "Login failed for user 'JoeUser'. Reason: The account is disabled." Additionally, the error is written to the Windows Event Log and can be viewed in the Windows Event Viewer.

Enabling the login can be done with the following statement.

ALTER LOGIN JoeUser ENABLE

To clean up our example, the following statement may be used to remove the SQL Server login.

DROP USER JoeUser
DROP LOGIN JoeUser

Now if someone attempts to login using the JoeUser login, "Login failed for user 'JoeUser'" is returned and error 18456 is written to the Windows Event Log.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Wednesday, October 10, 2007 7:04 AM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: Enabling and disabling logins in SQL Server 2005

can i restrict the created user to connect to the DB engine just from localhost and not from external? for a specific user only, not all users...
3/17/2009 1:23 AM | mnaji
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

try endpoints

http://translate.googleusercontent.com/translate_c?hl=cs&ie=UTF-8&sl=cs&tl=en&u=http://weboveaplikace.info/2010/01/25/zvyseni-bezpecnosti-mssql-serveru-pomoci-koncovych-bodu/&prev=_t&rurl=translate.google.com&twu=1&usg=ALkJrhiJjCnIIXjMsgnbQq7HIMkdSSTkig
1/26/2010 3:42 AM | Kazan
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

your website is "best" for solving our sql server problems
5/7/2010 10:31 PM | PIYUSH DUDHATRA
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

your website is "best" for solving our sql server & other problems
5/7/2010 10:33 PM | PIYUSH DUDHATRA
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

Thanks Piyush! I'm glad you've found it useful!

By the way, I'm not blogging at http://www.webbtechsolutions.com/blog
5/12/2010 6:30 PM | Joe Webb
Gravatar

# disable sql login after three failed login attempts


I am working on sql server 2005. I need to disable the login if the user fails 3 times.

Is it possible???
One more thing, does SQL server 2005 inherits computer security policy... If it does how do i configure????

Please help me i need to get this task done!

Thanks in advance.
9/9/2010 10:38 AM | Hélio Jacinto
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

Now if someone attempts to login using the JoeUser login, "Login failed for user 'JoeUser'" is returned and error 18456 is written to the Windows Event Log.
10/15/2010 12:41 AM | snow boots for women
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

Enabling the login can be done with the following statement.
10/15/2010 12:43 AM | columbia jackets
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

we switch to the AdventureWorks database and create a new user for the database. If we attempt to login to SQL Server using these credentials, we can.


snow boots | snow boots for women | columbia sportswear | columbia sportswear outlet | cheap north face jackets | the north face outlet | mac makeup | cheap makeup
10/19/2010 4:37 AM | furry boots
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

This super video converter for mac is developed by Emicsoft Studio, it is currently the best video converter running under Mac os x, comparied by isqunite, Visualhub and other Video Converter for Mac Free vide under simple video editing function embedded, support Even Thought This video converter for mac is a shareware, it's unregistered version have very little limitation o
10/26/2010 1:46 AM | hanly
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

Thanks for the information provided. I had two users disabled under secutirty. After running the queries it worked fine for me.
9/5/2011 6:26 AM | Neel
Gravatar

# re: Enabling and disabling logins in SQL Server 2005

To disable and deny login use commands

a): DENY CONNECT SQL TO @login
b): ALTER LOGIN @login DISABLE

12/7/2011 12:56 AM | LalitM
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET