{"id":10368,"date":"2019-03-12T20:10:33","date_gmt":"2019-03-12T20:10:33","guid":{"rendered":"https:\/\/dbtut.com\/?p=10368"},"modified":"2020-10-02T08:33:11","modified_gmt":"2020-10-02T08:33:11","slug":"how-to-create-sql-user-without-login","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/","title":{"rendered":"How To Create SQL User Without Login"},"content":{"rendered":"

We can create a user without login since SQL Server 2005 version. This feature was introduced instead of Application Role. You may want to read my article “What is Application Role in SQL Server<\/a>”<\/p>\n

Usually login and password information is written in the connection string and all users use the same username and password. When an application developer leave from work, the password and the connection string must be changed.<\/p>\n

We create a user without login and grant necessary privileges to the user. Then, we grant impersonate permission on this user to application developers. Thus, application developers can act as if this user. Let’s explain the subject by making an example.<\/p>\n

Let’s create a login named ApplicationDeveloperLogin on the Instance and only grant public permission to ApplicationDeveloperLogin in the TestDB database.<\/p>\n

USE [master]\nGO\nCREATE LOGIN [ApplicationDeveloperLogin] WITH PASSWORD=N'Abc123', \nDEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON\nGO\nUSE [TestDB]\nGO\nCREATE USER [ApplicationDeveloperLogin] FOR LOGIN [ApplicationDeveloperLogin]\nGO\n<\/pre>\n
We will receive an error when we try to read a table in the Test database with this user we created.<\/p>\n
\"\"<\/p>\n
Create a user with db_owner privilege on the database. (sql user without login)<\/p>\n
Right click on “Users” and click “New User” on the Security tab of the TestDB database.<\/p>\n
\"\"<\/p>\n
Select “SQL user without login” from “User type:” section.<\/p>\n
Give a name to the user we will create from “User name:” section.<\/p>\n
Select the default schema information from “Default schema:” section.<\/p>\n\n
\"\"<\/p>\n
Go to the Membership section and grant db_owner privilege to this user on the database. If you wish, you can grant more specific privileges from the Owned Schemas and Securables sections.<\/p>\n
\"\"<\/p>\n
We have created a user with db_owner privilege in the Test database without “login”.<\/p>\n
Then, grant impersonate permission to the ApplicationDeveloperLogin named “SQL_User_Without_Login”.<\/p>\n
REVOKE IMPERSONATE ON USER::[SQL_User_Without_Login] TO [ApplicationDeveloperLogin]<\/pre>\n
After granting the necessary privileges, you will see that you will not receive an error when you perform the select operation again with ApplicationDeveloperLogin as follows.<\/p>\n
USE TESTDB\nGO\nExecute As User='SQL_User_Without_Login'\nGO\nSELECT * FROM [TestDB].[dbo].[MyTable]\n<\/pre>\n
If you did not execute the impersonete script above, you will receive an error as follows.<\/p>\n\n
Msg 15517, Level 16, State 1, Line 3<\/em><\/span><\/p>\n
Cannot execute as the database principal because the principal “[SQL_User_Without_Login]” does not exist, this type of principal cannot be impersonated, or you do not have permission.<\/em><\/span><\/p>\n
Msg 229, Level 14, State 5, Line 5<\/em><\/span><\/p>\n
The SELECT permission was denied on the object ‘MyTable’, database ‘TestDB’, schema ‘dbo’.<\/em><\/span><\/p>\n
Another way to connect to a database without a login is to convert the database to Contained Database. You may want to read my article “What is Contained Database<\/a>“.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
We can create a user without login since SQL Server 2005 version. This feature was introduced instead of Application Role. You may want to read my article “What is Application Role in SQL Server” Usually login and password information is written in the connection string and all users use the same username and password. When …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[3],"tags":[2492,2510,2509,2493],"class_list":["post-10368","post","type-post","status-publish","format-standard","","category-mssql","tag-cannot-execute-as-the-database-principal-because-the-principal-does-not-exist","tag-grant-impersonate","tag-impersonate-user","tag-this-type-of-principal-cannot-be-impersonated"],"aioseo_notices":[],"yoast_head":"\nHow To Create SQL User Without Login - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Create SQL User Without Login\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Create SQL User Without Login - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Create SQL User Without Login\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-03-12T20:10:33+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-10-02T08:33:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png\" \/>\n<meta name=\"author\" content=\"dbtut\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"dbtut\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Create SQL User Without Login\",\"datePublished\":\"2019-03-12T20:10:33+00:00\",\"dateModified\":\"2020-10-02T08:33:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\"},\"wordCount\":405,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png\",\"keywords\":[\"Cannot execute as the database principal because the principal does not exist\",\"grant impersonate\",\"impersonate user\",\"this type of principal cannot be impersonated\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\",\"name\":\"How To Create SQL User Without Login - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png\",\"datePublished\":\"2019-03-12T20:10:33+00:00\",\"dateModified\":\"2020-10-02T08:33:11+00:00\",\"description\":\"How To Create SQL User Without Login\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png\",\"width\":843,\"height\":208},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Create SQL User Without Login\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/dbtut.com\/#website\",\"url\":\"https:\/\/dbtut.com\/\",\"name\":\"Database Tutorials\",\"description\":\"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux\",\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/dbtut.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/dbtut.com\/#organization\",\"name\":\"dbtut\",\"url\":\"https:\/\/dbtut.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg\",\"width\":223,\"height\":36,\"caption\":\"dbtut\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\",\"name\":\"dbtut\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g\",\"caption\":\"dbtut\"},\"description\":\"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.\",\"sameAs\":[\"http:\/\/NurullahCAKIR\"],\"url\":\"https:\/\/dbtut.com\/index.php\/author\/dbtut\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Create SQL User Without Login - Database Tutorials","description":"How To Create SQL User Without Login","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:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/","og_locale":"en_US","og_type":"article","og_title":"How To Create SQL User Without Login - Database Tutorials","og_description":"How To Create SQL User Without Login","og_url":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/","og_site_name":"Database Tutorials","article_published_time":"2019-03-12T20:10:33+00:00","article_modified_time":"2020-10-02T08:33:11+00:00","og_image":[{"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png","type":"","width":"","height":""}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Create SQL User Without Login","datePublished":"2019-03-12T20:10:33+00:00","dateModified":"2020-10-02T08:33:11+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/"},"wordCount":405,"commentCount":1,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png","keywords":["Cannot execute as the database principal because the principal does not exist","grant impersonate","impersonate user","this type of principal cannot be impersonated"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/","url":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/","name":"How To Create SQL User Without Login - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png","datePublished":"2019-03-12T20:10:33+00:00","dateModified":"2020-10-02T08:33:11+00:00","description":"How To Create SQL User Without Login","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/03\/img_5c8751dc45baf.png","width":843,"height":208},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/03\/12\/how-to-create-sql-user-without-login\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Create SQL User Without Login"}]},{"@type":"WebSite","@id":"https:\/\/dbtut.com\/#website","url":"https:\/\/dbtut.com\/","name":"Database Tutorials","description":"MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux","publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbtut.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/dbtut.com\/#organization","name":"dbtut","url":"https:\/\/dbtut.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2021\/02\/dbtutlogo.jpg","width":223,"height":36,"caption":"dbtut"},"image":{"@id":"https:\/\/dbtut.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408","name":"dbtut","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c322c32021bf651d9e103b183963c479a9c9791ead0715f4348203496c39aa54?s=96&d=mm&r=g","caption":"dbtut"},"description":"We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.","sameAs":["http:\/\/NurullahCAKIR"],"url":"https:\/\/dbtut.com\/index.php\/author\/dbtut\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/10368","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/comments?post=10368"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/10368\/revisions"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=10368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=10368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=10368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}