{"id":1255,"date":"2020-01-07T07:25:00","date_gmt":"2020-01-07T13:25:00","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=1255"},"modified":"2019-12-20T13:32:02","modified_gmt":"2019-12-20T19:32:02","slug":"sql-scripts-list-databases-a-login-can-access","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/","title":{"rendered":"SQL Scripts: List Databases A Login Can Access"},"content":{"rendered":"\n<div class=\"wp-block-image is-style-default\"><figure class=\"aligncenter size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"682\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280-1024x682.jpg\" alt=\"\" class=\"wp-image-1272\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280-1024x682.jpg 1024w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280-300x200.jpg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280-768x512.jpg 768w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg 1280w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure><\/div>\n\n\n\n<p>When you are managing your SQL Server database permissions through Active Directory Groups (which you should be!), it can sometimes be difficult to know just which databases a user actually has access to.  Sure, you could look up each AD Group that user is a member of and then check the permissions for each group, but who has time for that?  Below is a quick and dirty query that I wrote to tell me which databases a specified user can access.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Checking Your Access<\/h3>\n\n\n\n<p>By running this query, it will list every database on the server that YOU have access to.  <\/p>\n\n\n<pre class=\"lang:tsql decode:true  \">SELECT [name]\nFROM MASTER.sys.databases\nWHERE HAS_DBACCESS([name]) = 1<\/pre>\n\n\n<p>The key to this query is the <a rel=\"noreferrer noopener\" aria-label=\"HAS_DBACCESS (opens in a new tab)\" href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/has-dbaccess-transact-sql\" target=\"_blank\">HAS_DBACCESS<\/a> function.  This function returns information about whether the user running the query has access to the specified database. Per the documentation, it behaves in the following ways:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>HAS_DBACCESS returns 1 if the user has access to the database, 0 if the user has no access to the database, and NULL if the database name is not valid.<br>HAS_DBACCESS returns 0 if the database is offline or suspect.<br>HAS_DBACCESS returns 0 if the database is in single-user mode and the database is in use by another user.<\/p><cite><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/functions\/has-dbaccess-transact-sql\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"Microsoft SQL Docs (opens in a new tab)\">Microsoft SQL Docs<\/a><\/cite><\/blockquote>\n\n\n\n<p>It&#8217;s important to note that this query doesn&#8217;t tell you <em>WHAT <\/em>permissions a user has, it just returns a boolean &#8220;yes&#8221; or &#8220;no&#8221; value as to whether a user can access the specified database. <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Checking A User&#8217;s Access<\/h3>\n\n\n\n<p>Now that we have a query that can check database access, we need to be able to run it for a different user.  This can be done by specifying <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/execute-as-transact-sql\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\">EXECUTE AS LOGIN<\/a> just before the query. (also be sure to specify <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/revert-transact-sql\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\" (opens in a new tab)\">REVERT <\/a>after the query runs)<\/p>\n\n\n<pre class=\"lang:tsql decode:true  \">EXECUTE AS LOGIN = 'YourDomain\\User.Name' --Change This\n\tSELECT [name]\n\tFROM MASTER.sys.databases\n\tWHERE HAS_DBACCESS([name]) = 1\nREVERT<\/pre>\n\n\n<p>Now our query will check the access of each database, running as the user specified in the EXECUTE AS statement, and only return a list of databases that they specified user does have access to.  This will work for SQL Server Logins as well as Active Directory Logins.  As mentioned before, this query only shows you what databases the user can access, not the permissions the user has on the databases.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Logins Vs. Users<\/h3>\n\n\n\n<p>If you look at the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/execute-as-transact-sql\">EXECUTE AS<\/a> documentation, you will see that you have the option to run EXECUTE AS <em><span style=\"text-decoration: underline;\">LOGIN<\/span><\/em> or EXECUTE AS <em><span style=\"text-decoration: underline;\">USER<\/span><\/em>.  The difference between these 2 can sometimes be a little confusing, so here is a simplified example that I found on the interwebs:<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>A &#8220;Login&#8221; grants the principal entry into the SERVER.<\/p><p>A &#8220;User&#8221; grants a login entry into a single DATABASE.<\/p><p>One &#8220;Login&#8221; can be associated with many users (one per database).<\/p><cite><a href=\"https:\/\/stackoverflow.com\/questions\/1134319\/difference-between-a-user-and-a-login-in-sql-server\" target=\"_blank\" rel=\"noreferrer noopener\" aria-label=\"StackOverflow (opens in a new tab)\">StackOverflow<\/a><\/cite><\/blockquote>\n\n\n\n<p>What makes this tricky is that in the case of permissions being assigned to AD Groups, the specific user account you are testing will not have a SQL Server LOGIN or a Database USER.<\/p>\n\n\n\n<p>So, how does this work?  <\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>LOGIN<br>Specifies the execution context to be impersonated is a login. The scope of impersonation is at <strong>the server level<\/strong>.<\/p><p><\/p><p> USER<br>Specifies the context to be impersonated is a user <strong>in the current database<\/strong>. <\/p><cite><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/execute-as-transact-sql\">Microsoft SQL Docs<\/a><\/cite><\/blockquote>\n\n\n\n<p>As I notated in the quote above, LOGIN impersonates a user at the server level, and USER checks for the user at the database level. So, when checking the permissions of a user&#8217;s account that is in an AD Group, you will need to use EXECUTE AS <span style=\"text-decoration: underline;\"><em>LOGIN<\/em> <\/span>instead of EXECUTE AS <em><span style=\"text-decoration: underline;\">USER<\/span><\/em>.  As Microsoft states in the above listed documentation, the statement EXECUTE AS USER will fail because the user in the AD Group is not explicitly listed as a user in the database.<\/p>\n\n\n\n<p>Yeah, it&#8217;s confusing.  Just copy and paste the code above and see if it works.  \ud83d\ude09<\/p>\n\n\n\n<p><\/p>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you are managing your SQL Server database permissions through Active Directory Groups (which you should be!), it can sometimes be difficult to know just which databases a user actually &#8230;<\/p>\n","protected":false},"author":2,"featured_media":1272,"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":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[34,52],"tags":[35],"class_list":["post-1255","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-security","category-sql-scripts","tag-permissions"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Scripts: List Databases A Login Can Access - SQL Nuggets<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Scripts: List Databases A Login Can Access - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"When you are managing your SQL Server database permissions through Active Directory Groups (which you should be!), it can sometimes be difficult to know just which databases a user actually ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-07T13:25:00+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"853\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\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\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"SQL Scripts: List Databases A Login Can Access\",\"datePublished\":\"2020-01-07T13:25:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/\"},\"wordCount\":616,\"commentCount\":1,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/checklist-2470549_1280.jpg\",\"keywords\":[\"Permissions\"],\"articleSection\":[\"Security\",\"SQL Scripts\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/\",\"name\":\"SQL Scripts: List Databases A Login Can Access - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/checklist-2470549_1280.jpg\",\"datePublished\":\"2020-01-07T13:25:00+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/checklist-2470549_1280.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/checklist-2470549_1280.jpg\",\"width\":1280,\"height\":853},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/sql-scripts-list-databases-a-login-can-access\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Scripts: List Databases A Login Can Access\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Scripts: List Databases A Login Can Access - SQL Nuggets","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":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/","og_locale":"en_US","og_type":"article","og_title":"SQL Scripts: List Databases A Login Can Access - SQL Nuggets","og_description":"When you are managing your SQL Server database permissions through Active Directory Groups (which you should be!), it can sometimes be difficult to know just which databases a user actually ...","og_url":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/","og_site_name":"SQL Nuggets","article_published_time":"2020-01-07T13:25:00+00:00","og_image":[{"width":1280,"height":853,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"SQL Scripts: List Databases A Login Can Access","datePublished":"2020-01-07T13:25:00+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/"},"wordCount":616,"commentCount":1,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg","keywords":["Permissions"],"articleSection":["Security","SQL Scripts"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/","url":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/","name":"SQL Scripts: List Databases A Login Can Access - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg","datePublished":"2020-01-07T13:25:00+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg","width":1280,"height":853},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/sql-scripts-list-databases-a-login-can-access\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"SQL Scripts: List Databases A Login Can Access"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/12\/checklist-2470549_1280.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-kf","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":522,"url":"https:\/\/sqlnuggets.com\/sql-scripts-check-logins-permissions\/","url_meta":{"origin":1255,"position":0},"title":"SQL Scripts: How To Check Logins And Permissions","author":"Eric Cobb","date":"August 29, 2017","format":false,"excerpt":"Security and permissions are a big part of a DBA's job, and being able to find out things such as who has elevated login permissions, or when the last time a login was used is important. People sometimes get SQL Server\u00a0\"logins\" and \"users\" confused, or think they are the same\u2026","rel":"","context":"In &quot;Security&quot;","block_context":{"text":"Security","link":"https:\/\/sqlnuggets.com\/category\/security\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/security-265130_1920-e1503945772529.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/security-265130_1920-e1503945772529.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/security-265130_1920-e1503945772529.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/security-265130_1920-e1503945772529.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/08\/security-265130_1920-e1503945772529.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":545,"url":"https:\/\/sqlnuggets.com\/sql-scripts-change-owner-database\/","url_meta":{"origin":1255,"position":1},"title":"SQL Scripts: How To Change The Owner Of A Database","author":"Eric Cobb","date":"September 19, 2017","format":false,"excerpt":"Sometimes it becomes necessary to change the owner of a database. \u00a0When a database is first created, SQL Server likes to set the owner of that database to the login of whoever created it. \u00a0This also seems to be the case when restoring a database,\u00a0the owner is set to the\u2026","rel":"","context":"In &quot;Security&quot;","block_context":{"text":"Security","link":"https:\/\/sqlnuggets.com\/category\/security\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/09\/private-864304_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":641,"url":"https:\/\/sqlnuggets.com\/sql-server-permissions-manager\/","url_meta":{"origin":1255,"position":2},"title":"SQL Server Permissions Manager","author":"Eric Cobb","date":"November 21, 2017","format":false,"excerpt":"Last week I announced\u00a03 new open source SQL Server projects that I have on GitHub.\u00a0 One of those projects is SQL Server Permissions Manager, and today I would like to discuss it\u00a0in more detail. SQL Server Permissions Manager is a suite of scripts that allows you to take \"snapshots\" of\u2026","rel":"","context":"In &quot;Open Source Projects&quot;","block_context":{"text":"Open Source Projects","link":"https:\/\/sqlnuggets.com\/category\/open-source-projects\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/arrows-1577983_1280-e1511283424596.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":775,"url":"https:\/\/sqlnuggets.com\/sql-scripts-how-to-find-a-databases-compatibility-level\/","url_meta":{"origin":1255,"position":3},"title":"SQL Scripts: How To Find A Database\u2019s Compatibility Level","author":"Eric Cobb","date":"April 17, 2018","format":false,"excerpt":"One thing that a lot of DBAs, especially Accidental DBAs such as myself, don't seem to realize is that just because a database is sitting on a specific version of SQL Server does not mean that database has all of the features of that SQL Server version enabled.\u00a0 To put\u2026","rel":"","context":"In &quot;DB Engine&quot;","block_context":{"text":"DB Engine","link":"https:\/\/sqlnuggets.com\/category\/db-engine\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/04\/black-and-white-blank-challenge-262488-e1523887336460.jpg?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":576,"url":"https:\/\/sqlnuggets.com\/3-new-open-source-sql-server-projects\/","url_meta":{"origin":1255,"position":4},"title":"3 New Open Source SQL Server Projects","author":"Eric Cobb","date":"November 14, 2017","format":false,"excerpt":"Today I would like to officially unveil 3 new open source SQL Server projects that I have on GitHub!\u00a0 I will have upcoming blog posts that discuss each project in more details, but here is a brief summary of each: SQL Server Metrics Pack -\u00a0A collection of scripts for gathering\u2026","rel":"","context":"In &quot;Open Source Projects&quot;","block_context":{"text":"Open Source Projects","link":"https:\/\/sqlnuggets.com\/category\/open-source-projects\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/pexels-photo-211122-e1510630408305.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/pexels-photo-211122-e1510630408305.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/pexels-photo-211122-e1510630408305.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/pexels-photo-211122-e1510630408305.jpeg?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":243,"url":"https:\/\/sqlnuggets.com\/stored-procedures-ignore-users-permissions\/","url_meta":{"origin":1255,"position":5},"title":"Stored Procedures Ignore User&#8217;s Permissions","author":"Eric Cobb","date":"July 13, 2016","format":false,"excerpt":"So, that title may be a little misleading to some. It is not meant as a blanket statement about all procedures and all permissions, but rather a specific situation that I encountered recently. I ran into a case where a user was able access data in a table, even though\u2026","rel":"","context":"In &quot;Security&quot;","block_context":{"text":"Security","link":"https:\/\/sqlnuggets.com\/category\/security\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=1255"}],"version-history":[{"count":0,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/1255\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/1272"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=1255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=1255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=1255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}