{"id":1755,"date":"2014-01-03T11:00:38","date_gmt":"2014-01-03T16:00:38","guid":{"rendered":"http:\/\/sqlity.net\/en\/?p=1755"},"modified":"2014-11-13T12:01:37","modified_gmt":"2014-11-13T17:01:37","slug":"sql-server-database-users-sys-database_principals-catalog-view","status":"publish","type":"post","link":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/","title":{"rendered":"SQL Server Database Users and the sys.database_principals catalog view"},"content":{"rendered":"
\n

Introduction<\/h3>\n

\nYesterday we took a look at SQL Server's server principals<\/a>. Today we are going to look at the second level of the authentication hierarchy: The database users or database principals.\n<\/p>\n

Database principals cannot be used to directly connect to SQL Server. (SQL Server 2012 introduced contained databases that change this rule a little. We will discuss contained databases in a later post.) However, they can be used to grant or deny permissions on objects within a particular database.\n<\/p>\n

Logins and Users<\/h3>\n

\nWhen a login is created on a SQL Server instance, it does not have a lot of permissions out of the box. It can connect to the instance, but it cannot access any user database. To allow access to a user database a user needs to exist in that database that is linked to that same login. (There are exceptions to this rule if the login is a member of a server role with elevated permissions like the sysadmin fixed server role.)\n<\/p>\n

\nDuring the creation of a database user a login can be specified. The user will then be linked to that login. That means that any connection established with that login will be able to connect to the database that this user was created in and access objects based on the permissions granted to that user.\n<\/p>\n

Listing all database users<\/h3>\n

\nTo list all database users the sys.database_principals<\/span> catalog view can be utilized:\n<\/p>\n

\n[sql]\nSELECT DP.name,
\n DP.principal_id,
\n DP.type,
\n DP.type_desc,
\n DP.default_schema_name,
\n DP.create_date,
\n DP.modify_date,
\n DP.owning_principal_id,
\n DP.sid,
\n DP.is_fixed_role,
\n DP.authentication_type,
\n DP.authentication_type_desc,
\n DP.default_language_name,
\n DP.default_language_lcid
\nFROM sys.database_principals AS DP;
\n[\/sql]\n<\/div>\n

\nThe column list is very similar to the one of the sys.server_principals<\/span> catalog view that we discussed yesterday. Particularly interesting is the SID<\/span> column as it allows us to find the linked server principal:\n<\/p>\n

\n[sql]\nSELECT DP.name,
\n DP.principal_id,
\n DP.type_desc,
\n DP.default_schema_name,
\n DP.create_date,
\n DP.modify_date,
\n SP.name AS login_name,
\n SP.type_desc AS login_type_desc
\nFROM sys.database_principals AS DP
\nLEFT JOIN sys.server_principals AS SP
\n ON DP.sid = SP.sid;
\n[\/sql]\n<\/div>\n

\nThe JOIN has to be an outer join as you might have users that are not liked to a login.\n<\/p>\n

Database Principal Types<\/h3>\n

\nAs with server principals, there are three main types of database principals: users, groups and certificate based principals. Users are used to grant database level permissions to a linked login. They can be based on a SQL Login or on a Windows Login.\n<\/p>\n

\nDatabase principals can be linked to a server principal that represents a windows group. On the other hand, Server roles cannot be used to create a user. Their purpose is exclusively to ease management of server level permissions. Instead there is a database role. Database roles also are not based on a login as their sole purpose is to group several users of the database together to simplify database level permissions.\n<\/p>\n

\nAdditionally to those two group type principals there is a third one: The application role. Application roles will be discussed in a later article.\n<\/p>\n

\nLike server principals, database principals can be created using a certificate or asymmetric key. Those principals can be used to grant database level permissions to T-SQL modules. How this works exactly I will demonstrate in a separate post.\n<\/p>\n

\nTo find out the type of a given user you can consult the type_desc<\/span> column of the sys.database_principals<\/span> catalog view.\n<\/p>\n

Summary<\/h3>\n

\nDatabase principals are defined within a specific database and can be used to grant database level permissions. There are seven types of databases principals that can be categorized in three main categories: Users, groups and certificate base principals.\n<\/p>\n

\nA login can be linked to a database user and any connection using that login will get the permissions granted to its linked user while accessing objects in that database.\n<\/p>\n

\nThe sys.database_principals<\/span> catalog view returns one row for each database principal defined in the current database.\n<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.<\/p>\n

[more…]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[118,5,34,119],"tags":[49,50,38,15],"class_list":["post-1755","post","type-post","status-publish","format-standard","hentry","category-dmvs-cvs","category-general","category-security","category-security-dmvs-cvs","tag-certificates","tag-permission","tag-security-2","tag-sql-server"],"acf":[],"yoast_head":"\nSQL Server Users & the sys.database_principals view - sqlity.net<\/title>\n<meta name=\"description\" content=\"Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.\" \/>\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\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server Users & the sys.database_principals view - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/\" \/>\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-01-03T16:00:38+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-13T17:01:37+00:00\" \/>\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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/\"},\"author\":{\"name\":\"Sebastian Meine\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"headline\":\"SQL Server Database Users and the sys.database_principals catalog view\",\"datePublished\":\"2014-01-03T16:00:38+00:00\",\"dateModified\":\"2014-11-13T17:01:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/\"},\"wordCount\":709,\"commentCount\":1,\"keywords\":[\"Certificates\",\"Permission\",\"security\",\"SQL Server\"],\"articleSection\":[\"DMVs & CVs\",\"General\",\"Security\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/\",\"name\":\"SQL Server Users & the sys.database_principals view - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2014-01-03T16:00:38+00:00\",\"dateModified\":\"2014-11-13T17:01:37+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#\\\/schema\\\/person\\\/bcffd8c572bc2f1bd10fdba80135e53c\"},\"description\":\"Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/1755\\\/sql-server-database-users-sys-database_principals-catalog-view\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server Database Users and the sys.database_principals catalog view\"}]},{\"@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":"SQL Server Users & the sys.database_principals view - sqlity.net","description":"Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.","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\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server Users & the sys.database_principals view - sqlity.net","og_description":"Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.","og_url":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_published_time":"2014-01-03T16:00:38+00:00","article_modified_time":"2014-11-13T17:01:37+00:00","author":"Sebastian Meine","twitter_card":"summary_large_image","twitter_creator":"@sqlity","twitter_site":"@sqlity","twitter_misc":{"Written by":"Sebastian Meine","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/#article","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/"},"author":{"name":"Sebastian Meine","@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"headline":"SQL Server Database Users and the sys.database_principals catalog view","datePublished":"2014-01-03T16:00:38+00:00","dateModified":"2014-11-13T17:01:37+00:00","mainEntityOfPage":{"@id":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/"},"wordCount":709,"commentCount":1,"keywords":["Certificates","Permission","security","SQL Server"],"articleSection":["DMVs & CVs","General","Security","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/","url":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/","name":"SQL Server Users & the sys.database_principals view - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2014-01-03T16:00:38+00:00","dateModified":"2014-11-13T17:01:37+00:00","author":{"@id":"https:\/\/sqlity.net\/en\/#\/schema\/person\/bcffd8c572bc2f1bd10fdba80135e53c"},"description":"Database principals like the database user allow use to grant permissions on objects in a given database. Check out how to list users and find their linked logins.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/1755\/sql-server-database-users-sys-database_principals-catalog-view\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"SQL Server Database Users and the sys.database_principals catalog view"}]},{"@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_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2wXuw-sj","jetpack-related-posts":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1755","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=1755"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/posts\/1755\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1755"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/categories?post=1755"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/tags?post=1755"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}