{"id":2368,"date":"2012-11-27T14:46:40","date_gmt":"2012-11-27T14:46:40","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2368"},"modified":"2012-11-27T14:46:40","modified_gmt":"2012-11-27T14:46:40","slug":"schema","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2012\/11\/27\/schema\/","title":{"rendered":"schema"},"content":{"rendered":"<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nIn my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.\r\n\r\nIn today&#39;s post I will cover schemas. Schemas were introduced in SQL Server 2005, each schema is basically a distinct namespace in a database. A schema exists independently of the database user who created it. A schema is simply a container of objects. The owner of a schema can be any user, the ownership of the schema is transferable.\r\n\r\nLet&#39;s see how this all works, first create a new login name Denis with a highly secure password\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nUSE master\r\nGO\r\nCREATE LOGIN Denis WITH PASSWORD = &#39;Bla&#39;\r\nGO\r\n\r\n\r\nTo run all this code correctly, you should have two connections to the database we will create, one connection should be your admin connection, the other connection should be connected as this new user we just created.\r\n\r\nNow create a new database named SalesStuff\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nCREATE DATABASE SalesStuff\r\nGO\r\n\r\n\r\nInside the SalesStuff database create a new user which is mapped to the login Denis\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nUSE SalesStuff\r\nGO\r\nCREATE USER Denis FOR LOGIN Denis\r\nGO\r\n\r\n\r\nCreate a schema in the SalesStuff database named Sales, also create a table named Orders in that schema\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nCREATE SCHEMA Sales\r\nGO\r\nCREATE TABLE Sales.Orders\r\n(OrderID int, OrderDate date, OrderAmount decimal(30,2))\r\n\r\n\r\nNow login to the database with the Denis account and run the query below\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nselect * from orders\r\n\r\n\r\nYou should see the following error.\r\nMsg 208, Level 16, State 1, Line 1\r\nInvalid object name &#39;orders&#39;.\r\n\r\nThe problem is that when you login, your default schema is not Sales and so the Orders table can&#39;t be found. Prefix the table with the schema and try again\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nselect * from Sales.Orders\r\n\r\n\r\nYou get this error message\r\nMsg 229, Level 14, State 5, Line 1\r\nThe SELECT permission was denied on the object &#39;Orders&#39;, database &#39;SalesStuff&#39;, schema &#39;Sales&#39;.\r\n\r\nWe need to give the Denis user select permissions for this table. Login as the admin and run the query below\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nGRANT SELECT ON SCHEMA::Sales TO Denis\r\n\r\n\r\nThat query gave the user Denis select permissions on all tables in the Sales schema. Notice the double colon syntax, that is how you need to grant, deny and revoke permissions. If you run the select query again, you will get back an empty resultset.\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nselect * from Sales.Orders\r\n\r\n\r\nLet&#39;s try to do an insert\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\ninsert Sales.Orders values(1,getdate(),100)\r\n\r\n\r\nAs expected, that fails also\r\n\r\nMsg 229, Level 14, State 5, Line 1\r\nThe INSERT permission was denied on the object &#39;Orders&#39;, database &#39;SalesStuff&#39;, schema &#39;Sales&#39;.\r\n\r\nGo back to the admin query window, run the query below to give the insert permissions\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nGRANT INSERT ON SCHEMA::Sales TO Denis\r\n\r\n\r\nIf you try the insert again, it will succeed\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\ninsert Sales.Orders values(1,getdate(),100)\r\n\r\n\r\nRemember how we tried to select from the table without specifying the schema? Let&#39;s try that again\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nselect * from Orders\r\n\r\n\r\nMsg 208, Level 16, State 1, Line 1\r\nInvalid object name &#39;Orders&#39;.\r\n\r\nSame error, let&#39;s fix that\r\n\r\nGo back to the admin query window and execute the query below\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nALTER USER Denis\r\nWITH DEFAULT_SCHEMA = Sales\r\n\r\n\r\nWe just made the Sales schema the default schema for the user Denis. Now if we specify the schema or if we omit the schema, we get back the same result\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nselect * from Orders\r\nselect * from Sales.Orders\r\n\r\n\r\nGo back to the admin connection and create this stored procedure\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\ncreate procedure Sales.prtest1\r\nas\r\nselect 1\r\n\r\n\r\nGo to the query window for the user Denis and run the proc\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nexec prtest1\r\n\r\n\r\nMsg 229, Level 14, State 5, Procedure prtest1, Line 1\r\nThe EXECUTE permission was denied on the object &#39;prtest1&#39;, database &#39;SalesStuff&#39;, schema &#39;dbo&#39;.\r\n\r\nAs you can see, we don&#39;t have execute permissions for the stored procedure.\r\nBring up the admin query window and give Denis execute permissions on the schema\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nGRANT execute ON SCHEMA::Sales TO Denis\r\n\r\n\r\nNow if you try to execute the proc from the connection which is logged in as Denis it succeeds\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nexec prtest1\r\n\r\n\r\nGo back yet again to the admin query window and create another stored procedure\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\ncreate procedure Sales.prtest2\r\nas\r\nselect 2\r\n\r\n\r\nNow if you go back to the connection for user Denis and execute the proc we just created, it also is successful.\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nexec prtest2\r\n\r\n\r\nAs you can see, once you have execute permissions on a schema, you don&#39;t have to go and explicitly give execute permissions for every stored procedure\r\n\r\nTo see all the tables that you have select permissions on, you can run the query below from the connection logged in as Denis. It will return 1 if you have select permissions or 0 if you don&#39;t\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nSELECT HAS_PERMS_BY_NAME\r\n(QUOTENAME(SCHEMA_NAME(schema_id)) + &#39;.&#39; + QUOTENAME(name),\r\n    &#39;OBJECT&#39;, &#39;SELECT&#39;) AS have_select, name FROM sys.tables\r\n\r\n\r\nOutput\r\n---------------\r\n1\tOrders\r\n\r\nFor procs it will return 1 if you have execute permissions, if you don&#39;t have execute permissions then the proc is not returned. Run the query below from the connection logged in as Denis\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nSELECT HAS_PERMS_BY_NAME\r\n(QUOTENAME(SCHEMA_NAME(schema_id)) + &#39;.&#39; + QUOTENAME(name),\r\n    &#39;OBJECT&#39;, &#39;exec&#39;) AS have_select, name FROM sys.procedures\r\n\r\nOutput\r\n\r\n---------------\r\n1\tprtest1\r\n\r\n1\tprtest2\r\n\r\n\r\nAs you can see you get 2 rows back\r\n\r\nNo go back to the admin connection and deny execute on the schema\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nDENY EXECUTE ON SCHEMA::Sales TO Denis\r\n\r\n\r\nRun the query below from the connection logged in as Denis\r\n\r\nTSQL\r\nLINE NUMBER OFF | HIDE | SELECT ALL\r\nSELECT HAS_PERMS_BY_NAME\r\n(QUOTENAME(SCHEMA_NAME(schema_id)) + &#39;.&#39; + QUOTENAME(name),\r\n    &#39;OBJECT&#39;, &#39;exec&#39;) AS have_select, name FROM sys.procedures\r\n\r\n\r\nAs you can see nothing is returned at all\r\n\r\nSo what is so cool about schemas anyway?\r\nWhen you start using schemas, you have a way to logically group a bunch of objects together. For example if you have a Sales and a Marketing schema then if you need to find a specific table that has something to do with Sales, you don&#39;t have to look up and down in object explorer to find the table, it should be sorted under the sales schema. Permissions are also easier, you give the sales person permission to the Sales schema and if new tables are added he or she will have the select permission the moment the table is created.\r\nWhen using schemas you now can have a table named Customers in both schemas without a problem and each will hold data just for the department that uses the schema the table is in.\r\n\r\nRead more\r\nThis was just a small overview, I did not cover all the things you need to know about schemas in SQL Server. Take a look at SQL Server Best Practices &ndash; Implementation of Database Object Schemas to get some more details about how to use schemas.\r\n\r\n\u53c2\u8003\uff1a<a href=\"http:\/\/blogs.lessthandot.com\/index.php\/DataMgmt\/DBProgramming\/MSSQLServer\/sql-advent-2011-day-4\">http:\/\/blogs.lessthandot.com\/index.php\/DataMgmt\/DBProgramming\/MSSQLServer\/sql-advent-2011-day-4<\/a>\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In my Are you ready for SQL Server 2012 or are you stil [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2368","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2368","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2368"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2368\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2368"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2368"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2368"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}