{"id":13705,"date":"2019-11-16T14:02:14","date_gmt":"2019-11-16T14:02:14","guid":{"rendered":"https:\/\/dbtut.com\/?p=13705"},"modified":"2021-02-09T10:39:30","modified_gmt":"2021-02-09T10:39:30","slug":"restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/","title":{"rendered":"Differences Between sysadmin and CONTROL SERVER in SQL Server"},"content":{"rendered":"

We have been able to use Control Server Permission since SQL Server 2005. You can use this permission as an alternative to sysadmin. But it does not mean both are the same. As you know, sysadmin is a super user who can do anything on the sql server.<\/p>\n

The differences between sysadmin and control server can be found in the table below. These are the differences I see at the first moment. I’m sure there are other differences. But I could say that it was enough to make me stop.<\/p>\n

Differences Between Control server and sysadmin<\/h2>\n
\n\n\n\n\n\n\n\n\n
<\/td>\nControl Server<\/strong><\/td>\nsysadmin<\/strong><\/td>\n<\/tr>\n
Can we deny at Instance Level?<\/td>\nYes<\/td>\nNo<\/td>\n<\/tr>\n
Can it configure Database Mail?<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
Can it manage Jobs?<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
Can it create Linked Server?<\/td>\nNo<\/td>\nYes<\/td>\n<\/tr>\n
Can it impersonate other users with Sysadmin rights?<\/td>\nYes<\/td>\nYes<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

As you can see, the most important feature of Control Server permission is; It is able to impersonate sysadmins.<\/p>\n

You can see the server level rights of a Login that has Control Server permission:<\/p>\n

CONNECT SQL\r\nSHUTDOWN\r\nCREATE ENDPOINT\r\nCREATE ANY DATABASE\r\nCREATE AVAILABILITY GROUP\r\nALTER ANY LOGIN\r\nALTER ANY CREDENTIAL\r\nALTER ANY ENDPOINT\r\nALTER ANY LINKED SERVER\r\nALTER ANY EXTERNAL DATA SOURCE\r\nALTER ANY EXTERNAL FILE FORMAT\r\nALTER ANY CONNECTION\r\nALTER ANY DATABASE\r\nALTER RESOURCES\r\nALTER SETTINGS\r\nALTER TRACE\r\nALTER ANY AVAILABILITY GROUP\r\nADMINISTER BULK OPERATIONS\r\nAUTHENTICATE SERVER\r\nEXTERNAL ACCESS ASSEMBLY\r\nVIEW ANY DATABASE\r\nVIEW ANY DEFINITION\r\nVIEW SERVER STATE\r\nCREATE DDL EVENT NOTIFICATION\r\nCREATE TRACE EVENT NOTIFICATION\r\nALTER ANY EVENT NOTIFICATION\r\nALTER SERVER STATE\r\nUNSAFE ASSEMBLY\r\nALTER ANY SERVER AUDIT\r\nCREATE SERVER ROLE\r\nALTER ANY SERVER ROLE\r\nALTER ANY EVENT SESSION\r\nCONNECT ANY DATABASE\r\nIMPERSONATE ANY LOGIN\r\nCONTROL SERVER<\/pre>\n
Query SQL Server Instance Level Permissions<\/h3>\n
With the following query, you can see the permissions of the login you are connected to has:<\/p>\n
SELECT\u00a0entity_name,\u00a0permission_name\r\nFROM\u00a0sys.fn_my_permissions(NULL,\u00a0NULL)<\/pre>\n
For a clearer understanding, you must first understand the following permission types, which are announced with SQL Server 2014.<\/p>\n
CONNECT ANY DATABASE Permission in SQL Server<\/h3>\n
If you grant this permission to a login, it can connect to the databases on Instance, but has no authority over the databases. So you can not even select data from tables.<\/p>\n
IMPERSONATE ANY LOGIN Permission in SQL Server<\/h3>\n
If you grant this permission to a login, it may impersonate a sysadmin on Instance. Or vice versa, you can deny
\nto prevent someone who has CONTROL Server permission from impersonating sysadmin. \ud83d\ude42<\/p>\n
SELECT ALL USER SECURABLES Permission in SQL Server<\/h3>\n
If you grant this permission to a login, it can access data on any database it can connect to. If you grant SELECT ALL USER SECURABLES and CONNECT ANY DATABASE permission together, it will make sense.<\/p>\n
Consider a scenario that includes all of the permissions mentioned above.<\/p>\n
Suppose that there’s a new dba at work. And you want this DBA to be able to do management, but you don’t want him\/her to see the data.<\/p>\n
First, grant CONTROL Server permission to this login.<\/p>\n
Then, deny this login to prevent him\/her to viewing the data.(DENY SELECT ALL USER SECURABLES)<\/strong><\/p>\n
Then deny this login to prevent him\/her to impersonating a sysadmin on Instance.(DENY IMPERSONATE ANY LOGIN)<\/strong><\/p>\n
\"\"<\/p>\n
On the screen that appears, click on the Permission tab and click Search.<\/p>\n
\"\"<\/p>\n
On the screen that appears, in the “Select these object roles” section, when “Logins, Server Roles” is selected, click Browse.<\/p>\n
\"\"<\/p>\n
On the screen that appears, we find the controlserverLogin and click the box next to it and click ok.<\/p>\n
\"\"<\/p>\n
Again we see the previous screen. In this screen, we select controlserverLogin as follows and when controlserverLogin is selected, grant Control server permission to the login (click on the box below Grant). Finally click ok to complete the process.<\/p>\n
\"\"<\/p>\n
Capabilities of a Login that has Control Server Permission on the Instance<\/h3>\n
After granting control server permission to “controlserverLogin” at the instance level as above, we will test what it can do by connecting to the instance with this login.<\/p>\n
Can a Login that has Control Server Permission Manage SQL Server Agent?<\/h3>\n
As you can see first, we cannot see the SQL Server Agent. So a login that has Control Server permission can not manage SQL Server agent.<\/p>\n
\"\"<\/p>\n
Can a Login that has Control Server Permission Create a Linked Server?<\/h3>\n
When we click on the new linked server from Server Object, we receive an error like the following. But we are able to alter an existing linked server.<\/p>\n
A required operation could not be completed. You must be a member of the sysadmin role to perform this operation.<\/em><\/span><\/p>\n
\"\"<\/p>\n
Can a login that has Control Server Permission create a login and authorize it in a database?<\/h2>\n
As you can see in the script below, we are able to create a login and authorize it in a database.<\/p>\n
USE [master]\r\nGO\r\nCREATE LOGIN [controlservertest] WITH PASSWORD=N'xx', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF\r\nGO\r\nUSE [AdventureWorks2014]\r\nGO\r\nCREATE USER [controlservertest] FOR LOGIN [controlservertest]\r\nGO\r\nGRANT ALTER TO [controlservertest]\r\nGO<\/pre>\n
If we want to grant db_owner privileges to login using the following script, we receive the error as follows.<\/p>\n
USE [AdventureWorks2014]\r\nGO\r\nALTER ROLE [db_owner] ADD MEMBER [controlservertest]\r\nGO<\/pre>\n
Msg 15151, Level 16, State 1, Line 4<\/em><\/span><\/p>\n
Cannot alter the role ‘db_owner’, because it does not exist or you do not have permission.<\/em><\/span><\/p>\n
\"\"<\/p>\n
Can a Login that has Control Server Permissin Create a Database or read data in a database?<\/h3>\n
With ControlServerLogin we can read any data or create a database as follows.<\/p>\n
\"\"<\/p>\n
I wanted to briefly show a few features. I listed what you could do at the beginning of the article. It will be a bit hard to explain them all here one by one. You can try them all one by one.<\/p>\n
Restrict DBA From Viewing Data<\/h2>\n
If we go back to our topic; we want to prevent someone that has Control Server permission from seeing the data. That’s why we need to deny SELECT ALL USER SECURABLES at Instance level to prevent anyone with Control Server permission from reading the data.<\/p>\n
Right-click on Instance again and click on properties and select the controlserverLogin from the “Login and roles” section. And from the permission section below, select Select All User Securables and click on DENY.<\/p>\n
\"\"<\/p>\n
Now, when you try to read data with controlServerLogin, you receive the error as follows.<\/p>\n
Msg 229, Level 14, State 5, Line 2<\/em><\/span><\/p>\n
The SELECT permission was denied on the object ‘DatabaseLog’, database ‘AdventureWorks2014Yeni’, schema ‘dbo’.<\/em><\/span><\/p>\n
\"\"<\/p>\n
How To Impersonate a sysadmin with a login that has Control Server Permission<\/h3>\n
As a final step, I will end the article by explaining how controlserverLogin will impersonate a sysadmin, and how can we prevent it from impersonating sysadmin.<\/p>\n
We just received an error when we tried to read the data. Let’s try to read the data again by impersonating sysadminLogin.<\/p>\n
You can do this with the following script.<\/p>\n
EXECUTE AS LOGIN = 'sysadminLogin'\r\nGO\r\nSELECT *\r\n  FROM [AdventureWorks2014Yeni].[dbo].[DatabaseLog]<\/pre>\n
As you can see, while normally we can not read data, we were able to read data with controlserverLogin by impersonating sysadminLogin.<\/p>\n
In Stored Procedures, we can do the same as follows.<\/p>\n
CREATE PROCEDURE [dbo].[procedurename]\r\nWITH EXECUTE AS 'sysadminLogin'\r\nAS\r\n\r\n.\r\n.\r\n.<\/pre>\n
How To Deny Impersonate Permission from a Login that has Control server Permission<\/h3>\n
As I mentioned at the beginning of the article, we should DENY IMPERSONATE ANY LOGIN privilege at instance level. You must DENY the “Select All User Securables” permission.<\/p>\n\n<\/div>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
We have been able to use Control Server Permission since SQL Server 2005. You can use this permission as an alternative to sysadmin. But it does not mean both are the same. As you know, sysadmin is a super user who can do anything on the sql server. The differences between sysadmin and control server …<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":13723,"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":[5607,819,5630,5631,5627,5608,5603,5602,5601,5621,5622,5619,5618,5594,5593,5610,5613,5615,5616,5614,5617,5595,5632,5623,5625,5611,5612,5620,5600,5599,5598,5628,5624,5597,5596,5609,5606,5605,5604,5626,5629],"class_list":["post-13705","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-mssql","tag-a-required-operation-could-not-be-completed-you-must-be-a-member-of-the-sysadmin-role-to-perform-this-operation","tag-because-it-does-not-exist-or-you-do-not-have-permission","tag-can-a-login-that-has-control-server-permission-create-a-linked-server","tag-can-a-login-that-has-control-server-permission-create-a-login-and-authorize-it-in-a-database","tag-can-a-sql-server-dba-be-restricted-from-viewing-data","tag-cannot-alter-the-role-db_owner","tag-connect-any-database","tag-connect-any-database-permission","tag-connect-any-database-permission-in-sql-server","tag-control-server-permission","tag-control-server-permission-in-sql-server","tag-deny-impersonate","tag-deny-impersonate-permission-from-a-login","tag-difference-between-control-server-and-sysadmin","tag-differences-between-control-server-and-sysadmin","tag-execute-as-login","tag-execute-as-sysadmin","tag-execute-as-sysadmin-in-sp","tag-execute-as-sysadmin-in-sp-in-sql-server","tag-execute-as-sysadmin-in-sql-server","tag-execute-as-sysadmin-in-stored-procedure","tag-fn_my_permissions","tag-how-to-impersonate-a-sysadmin","tag-how-to-prevent-dbas-from-viewing-the-data","tag-how-to-prevent-sql-dbas-from-seeing-user-data","tag-impersonate-a-login","tag-impersonate-a-login-in-sql-server","tag-impersonate-a-sysadmin","tag-impersonate-any-login","tag-impersonate-any-login-permission","tag-impersonate-any-login-permission-in-sql-server","tag-prevent-dba-from-viewing-the-data","tag-prevent-dbas-from-viewing-the-data","tag-query-instance-level-permissions-in-sql-server","tag-query-sql-server-instance-level-permissions","tag-restrict-dba-from-viewing-data","tag-select-all-user-securables","tag-select-all-user-securables-permission","tag-select-all-user-securables-permission-in-sql-server","tag-stop-the-dba-reading-data","tag-you-can-see-the-server-level-rights-of-a-login-that-has-control-server-permission"],"aioseo_notices":[],"yoast_head":"\nDifferences Between sysadmin and CONTROL SERVER in SQL Server - Database Tutorials<\/title>\n<meta name=\"description\" content=\"This article contains information about Differences Between sysadmin and CONTROL SERVER permissions 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\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Differences Between sysadmin and CONTROL SERVER in SQL Server - Database Tutorials\" \/>\n<meta property=\"og:description\" content=\"This article contains information about Differences Between sysadmin and CONTROL SERVER permissions in SQL Server\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"Database Tutorials\" \/>\n<meta property=\"article:published_time\" content=\"2019-11-16T14:02:14+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-09T10:39:30+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png\" \/>\n\t<meta property=\"og:image:width\" content=\"541\" \/>\n\t<meta property=\"og:image:height\" content=\"318\" \/>\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=\"8 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\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\"},\"author\":{\"name\":\"dbtut\",\"@id\":\"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408\"},\"headline\":\"Differences Between sysadmin and CONTROL SERVER in SQL Server\",\"datePublished\":\"2019-11-16T14:02:14+00:00\",\"dateModified\":\"2021-02-09T10:39:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\"},\"wordCount\":1066,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/dbtut.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png\",\"keywords\":[\"A required operation could not be completed. You must be a member of the sysadmin role to perform this operation.\",\"because it does not exist or you do not have permission.\",\"Can a Login that has Control Server Permission Create a Linked Server\",\"Can a login that has Control Server Permission create a login and authorize it in a database\",\"Can a SQL Server DBA be restricted from viewing data\",\"Cannot alter the role 'db_owner'\",\"CONNECT ANY DATABASE\",\"CONNECT ANY DATABASE Permission\",\"CONNECT ANY DATABASE Permission in SQL Server\",\"Control Server Permission\",\"Control Server Permission in SQL Server\",\"Deny Impersonate\",\"Deny Impersonate Permission from a Login\",\"Difference Between Control server and sysadmin\",\"Differences Between Control server and sysadmin\",\"EXECUTE AS LOGIN\",\"Execute as sysadmin\",\"execute as sysadmin in sp\",\"execute as sysadmin in sp in SQL Server\",\"Execute as sysadmin in SQL Server\",\"execute as sysadmin in stored procedure\",\"fn_my_permissions\",\"How To Impersonate a sysadmin\",\"How to Prevent DBA's from viewing the data\",\"How to prevent SQL DBAs from seeing user data\",\"Impersonate a login\",\"Impersonate a login in SQL Server\",\"Impersonate a sysadmin\",\"IMPERSONATE ANY LOGIN\",\"IMPERSONATE ANY LOGIN Permission\",\"IMPERSONATE ANY LOGIN Permission in SQL Server\",\"Prevent DBA from viewing the data\",\"Prevent DBA's from viewing the data\",\"Query Instance Level Permissions in SQL Server\",\"Query SQL Server Instance Level Permissions\",\"Restrict DBA From Viewing Data\",\"SELECT ALL USER SECURABLES\",\"SELECT ALL USER SECURABLES Permission\",\"SELECT ALL USER SECURABLES Permission in SQL Server\",\"Stop The DBA Reading Data\",\"You can see the server level rights of a Login that has Control Server permission\"],\"articleSection\":[\"MSSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\",\"url\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\",\"name\":\"Differences Between sysadmin and CONTROL SERVER in SQL Server - Database Tutorials\",\"isPartOf\":{\"@id\":\"https:\/\/dbtut.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png\",\"datePublished\":\"2019-11-16T14:02:14+00:00\",\"dateModified\":\"2021-02-09T10:39:30+00:00\",\"description\":\"This article contains information about Differences Between sysadmin and CONTROL SERVER permissions in SQL Server\",\"breadcrumb\":{\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png\",\"contentUrl\":\"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png\",\"width\":541,\"height\":318},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/dbtut.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Differences Between sysadmin and CONTROL SERVER 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":"Differences Between sysadmin and CONTROL SERVER in SQL Server - Database Tutorials","description":"This article contains information about Differences Between sysadmin and CONTROL SERVER permissions 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\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Differences Between sysadmin and CONTROL SERVER in SQL Server - Database Tutorials","og_description":"This article contains information about Differences Between sysadmin and CONTROL SERVER permissions in SQL Server","og_url":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/","og_site_name":"Database Tutorials","article_published_time":"2019-11-16T14:02:14+00:00","article_modified_time":"2021-02-09T10:39:30+00:00","og_image":[{"width":541,"height":318,"url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png","type":"image\/png"}],"author":"dbtut","twitter_card":"summary_large_image","twitter_misc":{"Written by":"dbtut","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/"},"author":{"name":"dbtut","@id":"https:\/\/dbtut.com\/#\/schema\/person\/fc047c39e1e53dce28fc4253529ea408"},"headline":"Differences Between sysadmin and CONTROL SERVER in SQL Server","datePublished":"2019-11-16T14:02:14+00:00","dateModified":"2021-02-09T10:39:30+00:00","mainEntityOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/"},"wordCount":1066,"commentCount":0,"publisher":{"@id":"https:\/\/dbtut.com\/#organization"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png","keywords":["A required operation could not be completed. You must be a member of the sysadmin role to perform this operation.","because it does not exist or you do not have permission.","Can a Login that has Control Server Permission Create a Linked Server","Can a login that has Control Server Permission create a login and authorize it in a database","Can a SQL Server DBA be restricted from viewing data","Cannot alter the role 'db_owner'","CONNECT ANY DATABASE","CONNECT ANY DATABASE Permission","CONNECT ANY DATABASE Permission in SQL Server","Control Server Permission","Control Server Permission in SQL Server","Deny Impersonate","Deny Impersonate Permission from a Login","Difference Between Control server and sysadmin","Differences Between Control server and sysadmin","EXECUTE AS LOGIN","Execute as sysadmin","execute as sysadmin in sp","execute as sysadmin in sp in SQL Server","Execute as sysadmin in SQL Server","execute as sysadmin in stored procedure","fn_my_permissions","How To Impersonate a sysadmin","How to Prevent DBA's from viewing the data","How to prevent SQL DBAs from seeing user data","Impersonate a login","Impersonate a login in SQL Server","Impersonate a sysadmin","IMPERSONATE ANY LOGIN","IMPERSONATE ANY LOGIN Permission","IMPERSONATE ANY LOGIN Permission in SQL Server","Prevent DBA from viewing the data","Prevent DBA's from viewing the data","Query Instance Level Permissions in SQL Server","Query SQL Server Instance Level Permissions","Restrict DBA From Viewing Data","SELECT ALL USER SECURABLES","SELECT ALL USER SECURABLES Permission","SELECT ALL USER SECURABLES Permission in SQL Server","Stop The DBA Reading Data","You can see the server level rights of a Login that has Control Server permission"],"articleSection":["MSSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/","url":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/","name":"Differences Between sysadmin and CONTROL SERVER in SQL Server - Database Tutorials","isPartOf":{"@id":"https:\/\/dbtut.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png","datePublished":"2019-11-16T14:02:14+00:00","dateModified":"2021-02-09T10:39:30+00:00","description":"This article contains information about Differences Between sysadmin and CONTROL SERVER permissions in SQL Server","breadcrumb":{"@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#primaryimage","url":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png","contentUrl":"https:\/\/dbtut.com\/wp-content\/uploads\/2019\/11\/Ads\u0131z-26.png","width":541,"height":318},{"@type":"BreadcrumbList","@id":"https:\/\/dbtut.com\/index.php\/2019\/11\/16\/restrict-dba-from-viewing-data-and-differences-between-sysadmin-and-control-server-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbtut.com\/"},{"@type":"ListItem","position":2,"name":"Differences Between sysadmin and CONTROL SERVER 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\/13705","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=13705"}],"version-history":[{"count":0,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/posts\/13705\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media\/13723"}],"wp:attachment":[{"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/media?parent=13705"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/categories?post=13705"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbtut.com\/index.php\/wp-json\/wp\/v2\/tags?post=13705"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}