{"id":2322,"date":"2014-04-04T11:00:37","date_gmt":"2014-04-04T15:00:37","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=2322"},"modified":"2015-09-09T16:22:23","modified_gmt":"2015-09-09T20:22:23","slug":"change-password","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/2322\/change-password\/","title":{"rendered":"How to Change a SQL Login’s Password"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nSQL Server implements two types of authentication: Windows Authentication and SQL Authentication. Windows Authentication is often also called Integrated Security. Which authentication type is applied is dependent on the server principal used to log on. If the server principal in SQL Server is based on a Windows account or a Windows group, windows authentication has to be used. If the login is a SQL Login, SQL Authentication is applied. With windows Authentication, SQL Server delegates the actual authentication process to windows without ever touching a password. With SQL Authentication SQL Server has to check the password itself. That means that a SQL Login always has to have an associated password stored within SQL Server (if you want to be able to use it to log on).\n<\/p>\n

\nToday I am going to look at, how you can change that password.\n<\/p>\n

How to use ALTER LOGIN to Change a Password<\/h3>\n

\nTo change the password of a SQL Login, the ALTER LOGIN<\/span> command can be used. To demonstrate, we first need to create a login:\n<\/p>\n

\n[sql]\nCREATE LOGIN ALogin WITH PASSWORD = 'Passw0rd!';
\n[\/sql]\n<\/div>\n

\nThe syntax of the ALTER LOGIN<\/span> statement is very similar to the CREATE LOGIN<\/span> statement. To change the password of our login we can just replace the CREATE<\/span> keyword with ALTER<\/span>:\n<\/p>\n

<\/p>\n


\n\"Secrets<\/a>\n<\/div>\n

\n<\/div>\n

\n[sql]\nALTER LOGIN ALogin WITH PASSWORD = '2Secr@s';
\n[\/sql]\n<\/div>\n

\nThe following screenshot shows that in action:\n<\/p>\n

\n\"ALTER<\/a>\n<\/p>\n

\nSQL Server stores the password as a salted<\/a> hash<\/a>, so we cannot just look at the stored password. However, we can see that hash value. The screenshot shows the before and after value to demonstrate that the password did indeed change.\n<\/p>\n

Changing Your Own Password<\/h3>\n

\nTo change the password of any SQL Login on the server, you need to have been granted the ALTER ANY LOGIN<\/span> server privilege. However, you would expect that you could change your own password. Let us try:\n<\/p>\n

\n[sql]\nEXECUTE AS LOGIN = 'ALogin';
\nGO
\nALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?';
\nGO
\nREVERT;
\n[\/sql]\n<\/div>\n

\nExecuting this fails:\n<\/p>\n

\n\"Alter<\/a>\n<\/p>\n

\nThe reason is that in this context SQL Server requires the old password to also be specified, like this:\n<\/p>\n

\n[sql]\nALTER LOGIN ALogin WITH PASSWORD = 'Se6ure?' OLD_PASSWORD = '2Secr@s';
\n [\/sql]\n<\/div>\n

\nThat now leads to a successful password change:\n<\/p>\n

\n\"ALTER<\/a>\n<\/p>\n

\nWhile this works, it feels a little odd. If I have authenticated using my password already, why do I need to re-authenticate for a password change again? The reason is that Microsoft wanted to prevent principals that just have the IMPERSONATE<\/span> privilege on a login to be able to change the password for that login. In the example above I used EXECUTE AS<\/span> and did not anywhere supply the password.\n<\/p>\n

\nAs every login has control permission on itself, that privilege alone cannot be enough to change a login's password. Therefore you have to specify the current password if you want to change your own password. You actually can change any login's password, as long as you have the CONTROL<\/span> orALTER<\/span> privilege on that login and you now its current password.\n<\/p>\n

If you on the other hand have been granted the ALTER ANY LOGIN<\/span> server privilege, you can change any login's password without specifying the current one, even if you change your own:\n<\/p>\n

\n\"ALTER<\/a>\n<\/p>\n

Changing the Password for a Windows Login<\/h3>\n

\nIn the case of a windows login, SQL Server delegates the authentication to Windows. SQL Server never gets to store or even see a password. That means you cannot change a Windows account password from within SQL Server. If you tried to use ALTER LOGIN ... WITH PASSWORD<\/span> on a windows login, you would get this error message:\n<\/p>\n

\n[sourcecode]\nMsg 15080, Level 16, State 1, Line 1
\nCannot use parameter PASSWORD for a Windows login.
\n[\/sourcecode]\n<\/div>\n

\nTo actually change the password of a windows account, you have to use any documented windows method<\/a>.\n<\/p>\n

Summary<\/h3>\n

\nTo change the password of the SQL Login in SQL Server you can use the ALTER LOGIN ... WITH PASSWORD<\/span> command. To be able to change the password of any SQL Login you must have been granted the ALTER ANY LOGIN<\/span> server privilege. However, you always can change your own password by specifying the OLD_PASSWORD<\/span>.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":2325,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[29,5,34],"tags":[153,38,58,15],"class_list":["post-2322","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-fundamentals","category-general","category-security","tag-password","tag-security-2","tag-security-management","tag-sql-server"],"acf":[],"yoast_head":"\nHow to Change a SQL Login's Password - sqlity.net<\/title>\n<meta name=\"description\" content=\"ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/2322\/change-password\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Change a SQL Login's Password - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/2322\/change-password\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:published_time\" content=\"2014-04-04T15:00:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2015-09-09T20:22:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"779\" \/>\n\t<meta property=\"og:image:height\" content=\"490\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Sebastian Meine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@sqlity\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Sebastian Meine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"How to Change a SQL Login’s Password\",\"datePublished\":\"2014-04-04T15:00:37+00:00\",\"dateModified\":\"2015-09-09T20:22:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/\"},\"wordCount\":726,\"commentCount\":3,\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/ALTER_LOGIN_WITH_PASSWORD.jpg\",\"keywords\":[\"password\",\"security\",\"security management\",\"SQL Server\"],\"articleSection\":[\"Fundamentals\",\"General\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/\",\"name\":\"How to Change a SQL Login's Password - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/ALTER_LOGIN_WITH_PASSWORD.jpg\",\"datePublished\":\"2014-04-04T15:00:37+00:00\",\"dateModified\":\"2015-09-09T20:22:23+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/ALTER_LOGIN_WITH_PASSWORD.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlity.net\\\/wp-content\\\/uploads\\\/2014\\\/04\\\/ALTER_LOGIN_WITH_PASSWORD.jpg\",\"width\":779,\"height\":490,\"caption\":\"ALTER LOGIN ... WITH PASSWORD\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/2322\\\/change-password\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Change a SQL Login’s Password\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\",\"name\":\"Sebastian Meine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g\",\"caption\":\"Sebastian Meine\"},\"sameAs\":[\"http:\\\/\\\/sqlity.net\",\"https:\\\/\\\/x.com\\\/sqlity\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to Change a SQL Login's Password - sqlity.net","description":"ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/sqlity.net\/en\/2322\/change-password\/","og_locale":"en_US","og_type":"article","og_title":"How to Change a SQL Login's Password - sqlity.net","og_description":"ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.","og_url":"https:\/\/sqlity.net\/en\/2322\/change-password\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-04-04T15:00:37+00:00","article_modified_time":"2015-09-09T20:22:23+00:00","og_image":[{"width":779,"height":490,"url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg","type":"image\/jpeg"}],"author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"How to Change a SQL Login’s Password","datePublished":"2014-04-04T15:00:37+00:00","dateModified":"2015-09-09T20:22:23+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/"},"wordCount":726,"commentCount":3,"image":{"@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg","keywords":["password","security","security management","SQL Server"],"articleSection":["Fundamentals","General","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/2322\/change-password\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/","url":"https:\/\/sqlity.net\/en\/2322\/change-password\/","name":"How to Change a SQL Login's Password - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#primaryimage"},"image":{"@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg","datePublished":"2014-04-04T15:00:37+00:00","dateModified":"2015-09-09T20:22:23+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"ALTER LOGIN can be used to change the password of a SQL Login. Read on to see the required permissions and what you need to do to change your own password.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/2322\/change-password\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#primaryimage","url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg","contentUrl":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg","width":779,"height":490,"caption":"ALTER LOGIN ... WITH PASSWORD"},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/2322\/change-password\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"How to Change a SQL Login’s Password"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c","name":"Sebastian Meine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/4ab0a6d02dd494849a584a2c3c8bc3bdcef1d0aa5f87e98bf905dbdb9ad2ce3a?s=96&d=mm&r=g","caption":"Sebastian Meine"},"sameAs":["http:\/\/sqlity.net","https:\/\/x.com\/sqlity"]}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlity.net\/wp-content\/uploads\/2014\/04\/ALTER_LOGIN_WITH_PASSWORD.jpg","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-Bs","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=2322"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/2322\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media\/2325"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=2322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=2322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=2322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}