{"id":11596,"date":"2019-04-15T20:33:39","date_gmt":"2019-04-15T20:33:39","guid":{"rendered":"https:\/\/dbtut.com\/?p=11596"},"modified":"2020-08-18T09:05:23","modified_gmt":"2020-08-18T09:05:23","slug":"how-to-check-user-privileges-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/","title":{"rendered":"How To Check User Privileges in SQL Server"},"content":{"rendered":"<p>Due to the principle of &#8220;minimum authority for maximum security&#8221;, we should regularly check which user is authorized on the sql server, and revoke the unnecessary privileges from the users. In this article I will share scripts related to the following levels of authority.<\/p>\n<ul>\n<li>Server Level Authorized Users(Logins)<\/li>\n<li>Database LevelAuthorized Users(Logins)<\/li>\n<li>Schema Level Authorized Users(Logins)<\/li>\n<li>Object Level Authorized Users(Logins)<\/li>\n<\/ul>\n<h2>Server Level Authorized Users(Logins)<\/h2>\n<pre class=\"lang:default decode:true \">SELECT sp.name Server_Role, sp2.name Login_Name\nFROM sys.server_role_members srm\nJOIN sys.server_principals sp on sp.principal_id=srm.role_principal_id\nJOIN sys.server_principals sp2 on sp2.principal_id=srm.member_principal_id<\/pre>\n<p id=\"hoYHbkw\"><img loading=\"lazy\" decoding=\"async\" width=\"746\" height=\"471\" class=\"size-full wp-image-11623  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cb491a254f6e.png\" alt=\"\"><\/p>\n<h2>Database Level Authorized Users(Logins)<\/h2>\n<p>You can use below script to see database level authorized users. But it shows orphaned users too. So first you should check if you have orphaned users in your databases. Check the article; &#8220;<a href=\"https:\/\/dbtut.com\/index.php\/2018\/12\/26\/tip-sp_validatelogins-and-sp_change_users_login\/\" target=\"_blank\" rel=\"noopener noreferrer\">Tip: &#8220;sp_validatelogins&#8221; and &#8220;sp_change_users_login&#8221;(Orphaned Users in SQL Server)<\/a>&#8221;<\/p>\n<pre class=\"lang:default decode:true  \">Declare @database nvarchar(128)=null,\n\n@user varchar(20)=null,\n\n@dbo char(1)=null,\n\n@access char(1)=null,\n\n@security char(1)=null,\n\n@ddl char(1)=null,\n\n@datareader char(1)=null,\n\n@datawriter char(1)=null,\n\n@denyread char(1)=null,\n\n@denywrite char(1)=null\n\n\n\ndeclare @dbname varchar(200)\n\ndeclare @mSql1 varchar(8000)\n\nCREATE TABLE #DBROLES\n\n( DBName sysname not null, \n\nUserName sysname not null, \n\ndb_owner varchar(3) not null,\n\ndb_accessadmin varchar(3) not null,\n\ndb_securityadmin varchar(3) not null,\n\ndb_ddladmin varchar(3) not null,\n\ndb_datareader varchar(3) not null,\n\ndb_datawriter varchar(3) not null,\n\ndb_denydatareader varchar(3) not null,\n\ndb_denydatawriter varchar(3) not null,\n\nCur_Date datetime not null default getdate() \n\n)\n\n \n\nDECLARE DBName_Cursor CURSOR FOR \n\nselect name \n\nfrom master.dbo.sysdatabases \n\nwhere name not in ('mssecurity','tempdb')\n\nOrder by name\n\nOPEN DBName_Cursor\n\nFETCH NEXT FROM DBName_Cursor INTO @dbname\n\nWHILE @@FETCH_STATUS = 0\n\nBEGIN\n\nSet @mSQL1 = ' Insert into #DBROLES ( DBName, UserName, db_owner, db_accessadmin, \n\ndb_securityadmin, db_ddladmin, db_datareader, db_datawriter,\n\ndb_denydatareader, db_denydatawriter )\n\nSELECT '+''''+@dbName +''''+ ' as DBName ,UserName, '+char(13)+ ' \n\nMax(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,\n\nMax(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,\n\nMax(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,\n\nMax(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,\n\nMax(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,\n\nMax(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,\n\nMax(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,\n\nMax(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter\n\nfrom (\n\nselect b.name as USERName, c.name as RoleName \n\nfrom ' + @dbName+'.dbo.sysmembers a '+char(13)+ \n\n' join '+ @dbName+'.dbo.sysusers b '+char(13)+\n\n' on a.memberuid = b.uid join '+@dbName +'.dbo.sysusers c\n\non a.groupuid = c.uid )s \n\nGroup by USERName \n\norder by UserName'\n\n--Print @mSql1\n\nExecute (@mSql1)\n\nFETCH NEXT FROM DBName_Cursor INTO @dbname\n\nEND\n\nCLOSE DBName_Cursor\n\nDEALLOCATE DBName_Cursor\n\nSelect * from #DBRoles \n\nwhere ((@database is null) OR (DBName LIKE '%'+@database+'%')) AND\n\n((@user is null) OR (UserName LIKE '%'+@user+'%')) AND\n\n((@dbo is null) OR (db_owner = 'Yes')) AND\n\n((@access is null) OR (db_accessadmin = 'Yes')) AND\n\n((@security is null) OR (db_securityadmin = 'Yes')) AND\n\n((@ddl is null) OR (db_ddladmin = 'Yes')) AND\n\n((@datareader is null) OR (db_datareader = 'Yes')) AND\n\n((@datawriter is null) OR (db_datawriter = 'Yes')) AND\n\n((@denyread is null) OR (db_denydatareader = 'Yes')) AND\n\n((@denywrite is null) OR (db_denydatawriter = 'Yes'))\n<\/pre>\n<h2>Schema Level Authorized Users(Logins)<\/h2>\n<p>If you are using the always on availability group, it returns results for primary databases and also standalone databases.<\/p>\n<pre class=\"lang:default decode:true\">DECLARE @name&nbsp; NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX);\nDECLARE Crs CURSOR\nFOR\nSELECT name FROM sys.sysdatabases where dbid&gt;4 and name not in(\nSELECT  DB_NAME(dr_state.database_id) as name\nFROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) \nJOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id) \nJOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id \nand dr_state.replica_id = ar_state.replica_id\nwhere ar_state.role_desc='SECONDARY' AND ar_state.is_local=1\n)\nOPEN Crs\nFETCH NEXT FROM Crs INTO @Name\nWHILE @@FETCH_STATUS =0\nBEGIN&nbsp;&nbsp;&nbsp;\n\nSelect @Sql = 'Use ' + @name + ';\nif exists(select 1 FROM sys.database_permissions\n WHERE class_desc = ''SCHEMA'')\nBEGIN\nSELECT '''+@name+''' AS [DBName],SCHEMA_NAME(major_id) [SchemaName]\n     , USER_NAME(grantee_principal_id) [Login Name]\n     , permission_name [Privilege]\n FROM sys.database_permissions\n WHERE class_desc = ''SCHEMA''\n ORDER BY major_id, grantee_principal_id, permission_name\n END\n'\nprint @Sql\nExec sp_executesql @Sql&nbsp;\nprint @sql\n\n&nbsp;FETCH NEXT FROM Crs INTO @Name\n&nbsp;END&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\nCLOSE Crs\nDEALLOCATE Crs\n<\/pre>\n<p id=\"EahkgDF\"><img loading=\"lazy\" decoding=\"async\" width=\"673\" height=\"721\" class=\"size-full wp-image-11629  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cb4982bad9d8.png\" alt=\"\"><\/p>\n<h2>Object Level Authorized Users(Logins)<\/h2>\n<p>You can see these privileges for the single database with the following script.<\/p>\n<pre class=\"lang:default decode:true \">SELECT permission_name AS Privilege, type_desc [Object Type], \nU.name [Login Name], OBJECT_NAME(major_id) [Object Name]\nfrom sys.database_permissions dp\nJOIN sys.tables tbl ON dp.major_id = tbl.object_id \nJOIN sysusers u ON u.uid = dp.grantee_principal_id<\/pre>\n<p id=\"jzOzRYy\"><img loading=\"lazy\" decoding=\"async\" width=\"649\" height=\"294\" class=\"size-full wp-image-11628  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cb4947827d38.png\" alt=\"\"><\/p>\n<p>If you are using the always on availability group, you can use the below script for primary databases of availabilityg groups and also standalone databases on the instance.<\/p>\n<pre class=\"lang:default decode:true\">DECLARE @name&nbsp; NVARCHAR(MAX),@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX);\n\nDECLARE Crs CURSOR\nFOR\nSELECT name FROM sys.sysdatabases where dbid&gt;4 and name not in(\nSELECT  DB_NAME(dr_state.database_id) as name\nFROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) \nJOIN sys.dm_hadr_availability_replica_states AS ar_state ON ar.replica_id = ar_state.replica_id) \nJOIN sys.dm_hadr_database_replica_states dr_state on ag.group_id = dr_state.group_id \nand dr_state.replica_id = ar_state.replica_id\nwhere ar_state.role_desc='SECONDARY' AND ar_state.is_local=1\n)\nOPEN Crs\nFETCH NEXT FROM Crs INTO @Name\nWHILE @@FETCH_STATUS =0\nBEGIN&nbsp;&nbsp;&nbsp;\n\nSelect @Sql = 'Use ' + @name + ';\nif exists(SELECT 1 from sys.database_permissions dp\nJOIN sys.tables tbl ON dp.major_id = tbl.object_id \nJOIN sysusers u ON u.uid = dp.grantee_principal_id)\nBEGIN\nSELECT '''+@name+''' DBName,permission_name AS Privilege, type_desc [Object Type], U.name [Login Name], OBJECT_NAME(major_id) [Object Name]\nfrom sys.database_permissions dp\nJOIN sys.tables tbl ON dp.major_id = tbl.object_id \nJOIN sysusers u ON u.uid = dp.grantee_principal_id\nEND\n'\n\nExec sp_executesql @Sql&nbsp;\n\n&nbsp;FETCH NEXT FROM Crs INTO @Name\n&nbsp;END&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;\nCLOSE Crs\nDEALLOCATE Crs\n<\/pre>\n<p id=\"PoWggzB\"><img loading=\"lazy\" decoding=\"async\" class=\" wp-image-11630  aligncenter\" src=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/img_5cb4985358778.png\" alt=\"\" width=\"771\" height=\"587\"><\/p>\n\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_11596\" class=\"pvc_stats all  \" data-element-id=\"11596\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>Due to the principle of &#8220;minimum authority for maximum security&#8221;, we should regularly check which user is authorized on the sql server, and revoke the unnecessary privileges from the users. In this article I will share scripts related to the following levels of authority. Server Level Authorized Users(Logins) Database LevelAuthorized Users(Logins) Schema Level Authorized Users(Logins) &hellip;<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p id=\"pvc_stats_11596\" class=\"pvc_stats all  \" data-element-id=\"11596\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> <img loading=\"lazy\" decoding=\"async\" width=\"16\" height=\"16\" alt=\"Loading\" src=\"https:\/\/dbtut.com\/wp-content\/plugins\/page-views-count\/ajax-loader-2x.gif\" border=0 \/><\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":11632,"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":[3432,3425,3438,3433,3436,3435,3424,3434,3426,3429,3430,3437,3428,3431,3427],"class_list":["post-11596","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-azure-sql-check-user-permissions","tag-find-all-privileges","tag-list-all-permissions-for-a-login","tag-sql-server-check-user-permissions","tag-sql-server-check-user-permissions-on-object","tag-sql-server-check-user-permissions-on-sp","tag-sql-server-check-user-permissions-on-table","tag-sql-server-check-user-permissions-on-view","tag-sql-server-list-database-users-and-roles","tag-sql-server-login-permissions-script","tag-sql-server-permissions-list","tag-sql-server-query-to-find-all-permissions","tag-sql-server-role-permissions-query","tag-sql-server-user-permissions","tag-sql-server-user-roles-and-permissions"],"aioseo_notices":[],"a3_pvc":{"activated":true,"total_views":7936,"today_views":0},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v24.9 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How To Check User Privileges in SQL Server - Database Tutorials<\/title>\n<meta name=\"description\" content=\"How To Check User Privileges in SQL Server\" \/>\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\/04\/15\/how-to-check-user-privileges-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Check User Privileges in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"How To Check User Privileges in SQL Server\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-15T20:33:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-18T09:05:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png\" \/>\n\t<meta property=\"og:image:width\" content=\"766\" \/>\n\t<meta property=\"og:image:height\" content=\"353\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/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=\"6 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\/04\/15\/how-to-check-user-privileges-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"How To Check User Privileges in SQL Server\",\"datePublished\":\"2019-04-15T20:33:39+00:00\",\"dateModified\":\"2020-08-18T09:05:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/\"},\"wordCount\":200,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png\",\"keywords\":[\"azure sql check user permissions\",\"find all privileges\",\"List all permissions for a login\",\"sql server check user permissions\",\"sql server check user permissions on object\",\"sql server check user permissions on sp\",\"sql server check user permissions on table\",\"sql server check user permissions on view\",\"sql server list database users and roles\",\"sql server login permissions script\",\"sql server permissions list\",\"SQL Server query to find all permissions\",\"sql server role permissions query\",\"sql server user permissions\",\"sql server user roles and permissions\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/\",\"name\":\"How To Check User Privileges in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png\",\"datePublished\":\"2019-04-15T20:33:39+00:00\",\"dateModified\":\"2020-08-18T09:05:23+00:00\",\"description\":\"How To Check User Privileges in SQL Server\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png\",\"width\":766,\"height\":353},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Check User Privileges in SQL Server\"}]},{\"@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 Check User Privileges in SQL Server - Database Tutorials","description":"How To Check User Privileges in SQL Server","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\/04\/15\/how-to-check-user-privileges-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How To Check User Privileges in SQL Server - Database Tutorials","og_description":"How To Check User Privileges in SQL Server","og_url":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-04-15T20:33:39+00:00","article_modified_time":"2020-08-18T09:05:23+00:00","og_image":[{"width":766,"height":353,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"How To Check User Privileges in SQL Server","datePublished":"2019-04-15T20:33:39+00:00","dateModified":"2020-08-18T09:05:23+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/"},"wordCount":200,"commentCount":2,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png","keywords":["azure sql check user permissions","find all privileges","List all permissions for a login","sql server check user permissions","sql server check user permissions on object","sql server check user permissions on sp","sql server check user permissions on table","sql server check user permissions on view","sql server list database users and roles","sql server login permissions script","sql server permissions list","SQL Server query to find all permissions","sql server role permissions query","sql server user permissions","sql server user roles and permissions"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/","name":"How To Check User Privileges in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png","datePublished":"2019-04-15T20:33:39+00:00","dateModified":"2020-08-18T09:05:23+00:00","description":"How To Check User Privileges in SQL Server","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/04\/Ads\u0131z-31.png","width":766,"height":353},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/04\/15\/how-to-check-user-privileges-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"How To Check User Privileges in SQL Server"}]},{"@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\/11596","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=11596"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/11596\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/11632"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=11596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=11596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=11596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}