|
|
Browse by Tags
All Tags » SQL Server (RSS)
-
Securing SQL Server is one of the top priorities of any SQL Server DBA. It is extremely important for DBA’s and system admins to make sure that SQL Server is TIGHTLY SECURED and is not exposed to users that don’t need access to SQL Server. Only valid and required users MUST be granted “required” permissions. Apart from configuring the right security/permissions for SQL Server users/logins, it is also very important to select the right account to run SQL Server services. In today’s tip, we will explore when to use what service accounts for running SQL Server services. | WHEN TO USE DOMAIN USER ACCOUNT? | | If your SQL Server interacts with other servers, services or resources on the network (ex: Files Shares, etc.) or if your SQL Server services uses linked servers to connect to other SQL Servers on the network, then you may use a low privileged domain user account for running SQL Server services. Domain user account is the most recommended account for setting up SQL Server services that interact with other servers on the network. One of the plus points of using a Domain User Account is that the account is controlled by Windows active directory therefore, domain level policy on accounts apply to SQL Server service account as well. | | WHEN TO USE NETWORK SERVICE ACCOUNT? | | NEVER should you use Network Service Account for running SQL Server services. Network Service accounts are shared with other services running on the local computer. Network Service Account is a built-in account that has more access to server resources and objects than users accounts of local user groups. Any SQL Server services that runs on Network Service Account, can access network resources by using the credentials of the computer account. This account shows up as “NET AUTHORITY\NETWORK SERVICE” when configuring SQL Server Services. | | WHEN TO USE LOCAL USER ACCOUNT? | | If your SQL Server DOES NOT interact with other servers, services or resources on the network (ex: Files, Shares, Linked Servers, Etc.) then you may use a low privileged local user account for running SQL Server Services. NOTE: You are not required to use the local user account with administrative privileges to run SQL Server services. | | WHEN TO USE LOCAL SYSTEM ACCOUNT? | | NEVER should you use local system account for running SQL Server services. Local System Account has more permissions than you would think. It is a very high-privileged built-in account created by Windows O/S. Local System Account has extensive privileges on the entire local system and acts as a computer on your company’s network. This account shows up as “NT AUTHORITY\SYSTEM” when configuring SQL Server services. | 
|
-
You are a trusted database developer for your company. You developed a Stored Procedure that will perform some important functions on the database. You tested the Stored Procedure and everything looks great. The Stored Procedure runs the way you expected and within the anticipated timeframe. Everything looks great and now you are ready to deploy the code to the production environment. The production environment has same hardware as development/test environment therefore, you are confident that your procedure will run just fine. Deployment day approaches and you or your DBA team deploy the code to the production environment. The Stored Procedure is live but it is not performing the way you had expected. You are now wondering “what went wrong?”. You may have experienced something similar where the stored procedure/query performs bad when deployed on the production SQL Server and the same stored procedure/query works perfectly fine in the test/development environment. POSSIBLE CAUSE It is possible for Stored Procedure/queries to behave differently in Dev/Test and Production environments if you have not cleaned up the query execution plan cache from the dev/test environment while performing tests. Stored Procedures or queries will first try to use an existing plan cache at every execution and in the above scenario that may very well be happening. BACKGROUND When any SQL statement is executed, SQL Server first looks through the procedure cache (by querying SYS.DM_EXEC_CACHED_PLANS) to verify that an existing execution plan for the SQL statement exists. SQL Server reuses any existing plan it finds, saving the overhead of recompiling the same SQL statement. If it cannot find an execution plan, it generates a new execution plan for that query. BEST PRACTICE The best practice for deploying the code in your production environment is to make sure you first test the code in the test / development environment by removing cached query plans so that you know how your stored procedure or queries would perform in "Cold" cache which is almost like reproducing the cache as though SQL Server had just been started. CAUTION DO NOT USE THE BELOW COMMANDS IN THE PRODUCTION ENVIRONMENT, AS YOU COULD BE FLUSHING OUT SEVERAL CACHED QUERY EXECUTION PLANS THAT MAY PERFECTLY BE WORKING FINE. HOW TO: Please use the below commands on the test/development environment to clear out the cached plans for queries/stored procedures. CLEARNING A SPECIFIC QUERY CACHED PLAN: | Steps | Action | | 1 | EXECUTE THE QUERY YOU WOULD LIKE TO CLEAR THE CACHE FOR. SELECT * FROM CUSTOMERS | | 2 | OBTAIN THE PLAN_HANDLE FOR THE ABOVE QUERY SELECT PLAN_HANDLE, ST.TEXT FROM SYS.DM_EXEC_CACHED_PLANS CROSS APPLY SYS.DM_EXEC_SQL_TEXT(PLAN_HANDLE) AS ST WHERE TEXT LIKE 'SELECT * FROM CUSTOMERS%' GO | | 3 | TAKE THE PLAN HANDLE FROM THE ABOVE QUERY AND CLEAR THE CACHE DBCC FREEPROCCACHE (<PLAN_HANDLE>) | CLEARING ALL CACHED PLANS | Steps | Action | | 1 | CLEARNING THE CACHE AND LOGGING THE MESSAGE IN ERRORLOG DBCC FREEPROCCACHE | | | | | CLEARNING THE CACHE WITHOUT LOGGING THE MESSAGE IN ERRORLOG DBCC FREEPROCCACHE WITH NO_INFOMSGS; | HOW TO QUERY ALL CACHED PLANS FROM LOCAL INSTANCE OF SQL SERVER? | Steps | Action | | 1 | QUERY TO LIST ALL CACHED PLANS SELECT * FROM SYS.DM_EXEC_CACHED_PLANS | 
|
-
Many users have hated the way SQL Server supported configuring startup parameters in previous versions. It was confusing and buried in a single text box under the Advanced Tab of SQL Server services in Configuration Manager. The only way you could separate different options is by using a semicolon(;) between the options. With the introduction of SQL Server 2012, this is no longer an issue. The Startup Parameter option has been relocated to its own tab and can be easily accessed from SQL Server Configuration Manager. | CONFIGURING STARTUP PARAMETERS USING SQL SERVER 2012 | | 1. Launch SQL Server Configuration Manager from Configuration Tools folder 2. Click on SQL Server Services from the Left Pane. You should now see all the services associated with SQL Server on the right pane. 3. From the right pane, right-click on the “SQL Server Instance” you wish to configure startup options for and then click “Properties” 4. You will notice a new tab called Startup Parameter. You can now specify the startup option by typing the parameter in the “Specify a Startup Parameter” box. 5. Once done, click “OK” button. 6. Restart SQL Server Database Engine for changes to take effect. | | CONFIGURING STARTUP PARAMETERS USING PRIOR SQL SERVER VERSIONS (PRE SQL SERVER 2012) | | 1. In SQL Server Configuration Manager, click SQL Server Services. 2. In the right pane, right-click SQL Server (<instance_name>), and then click Properties. 3. On the Advanced tab, in the Startup Parameters box, type the parameters separated by semicolons (;). | BEST PRACTICES / THINGS TO KEEP IN MIND: 1. Always restart SQL Server services after making changes to startup options. 2. Never update/edit the SQL Server registry to add/edit any startup option. This could corrupt your SQL Server installation. 3. On a cluster, changes must be made on the active server when SQL Server is online, and will take effect when the Database Engine is restarted. The registry update of the startup options on the other node will occur upon the next failover.
|
-
I don’t often deal a lot with the business intelligence side of SQL Server preferring to spend most of my time on the relational side of the house. However, once in a while topics about optimizing a data warehouse or helping to secure BI data peak my interest. I ran across this article by Don Pinto, a SQL Server Security Product Manager at Microsoft and thought it was worth sharing. Enjoy!!
A common scenario in data warehousing applications is knowing what source system records to update, what data needs to be loaded and which data rows can be skipped as nothing has changed since they were last loaded. Another possible scenario is the need to facilitate searching data that is encrypted using cell level encryption or storing application passwords inside the database. Data Hashing can be used to solve this problem in SQL Server. A hash is a number that is generated by reading the contents of a document or message. Different messages should generate different hash values, but the same message causes the algorithm to generate the same hash value. | The HashBytes function in SQL Server | | SQL Server has a built-in function called HashBytes to support data hashing. HashBytes ( '<algorithm>', { @input | 'input' } )
<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 Here is a sample along with the return values commented in the next line : Select HashBytes('MD2', 'Hello world!') --0x63503D3117AD33F941D20F57144ECE64 Select HashBytes('MD4', 'Hello world!') --0x0D7A9DB5A3BED4AE5738EE6D1909649C Select HashBytes('MD5', 'Hello world!') --0x86FB269D190D2C85F6E0468CECA42A20 Select HashBytes('SHA', 'Hello world!') --0xD3486AE9136E7856BC42212385EA797094475802 Select HashBytes('SHA1', 'Hello world!') --0xD3486AE9136E7856BC42212385EA797094475802 Select HashBytes('SHA2_256', 'Hello world!') --0xC0535E4BE2B79FFD93291305436BF889314E4A3FAEC05ECFFCBB7DF31AD9 E51A Select HashBytes('SHA2_512', 'Hello world!') --0xF6CDE2A0F819314CDDE55FC227D8D7DAE3D28CC556222A0A8AD66D91CCA D4AAD6094F517A2182360C9AACF6A3DC323162CB6FD8CDFFEDB0FE038F55E8 5FFB5B6 | | | | Properties of good hash functions | | A good hashing algorithm has these properties: · It is especially sensitive to small changes in the input. Minor changes to the document will generate a very different hash result. · It is computationally unfeasible to reverse. There will be absolutely no way to determine what changed in the input or to learn anything about the content of an input by examining hash values. For this reason, hashing is often called one-way hashing. · It is very efficient. | | | | Should you encrypt or hash? | | During application development, it might be useful to understand when to encrypt your data vs. when to hash it.
The difference is that encrypted data can be decrypted, while hashed data cannot be decrypted. Another key difference is that encryption normally results in different results for the same text but hashing always produces the same result for the same text. The deciding factor when choosing to encrypt or hash your data comes after you determine if you'll need to decrypt the data for offline processing.
A typical example of data that needs to be decrypted would be within a payment processing system is a credit card number. Thus the credit card number should be encrypted in the payment processing system. However, in the case of security code for the credit card, hashing it is sufficient if only equality checks are done and the system does not need to know it’s real value. 
Encryption is a two way process but hashing is unidirectional | | How to use hash bytes for indexing encrypted data. | | Encryption introduces randomization and in there is no way to predict the outcome of an encryption built-in. Does that mean creating an index on top of encrypted data is not possible? However, data hashing can come to your rescue. Refer to this blog post to learn how. | | | | Which hash function should I choose? | | Although, most hashing functions are fast, the performance of a hashing function depends on the data to be hashed and the algorithm used. There is no magic bullet. For security purposes, it is advised to use the strongest hash function (SHA2_512). However, you can choose other hashing algorithms depending on your workload and data to hash. | | Hash functions or CHECK_SUM()? | | SQL Server has the CHECK_SUM () (or BINARY_CHECKSUM ()) functions for generating the checksum value computed over a row of a table, or over a list of expressions. One problem with the CHECK_SUM() (or BINARY_CHECKSUM()) functions is that the probability of a collision may not be sufficiently low for all applications (i.e. it is possible to come across examples of two different inputs hashing to the same output value). Of course, collisions are possible with any functions that have a larger domain than its range but because the CHECK_SUM function implements a simple XOR, the probability of this collision is high. Try it out using the following example - DECLARE @guid1 UNIQUEIDENTIFIER ,@guid2 UNIQUEIDENTIFIER SELECT @guid1 = ‘3DB7D309-A8F4-47C4-BA90-0CB458B44CB0′ , @guid2 = ‘EFE7F2C5-19F9-42B9-9C16-21BED41E882B’
SELECT chksum_guid1 = CHECKSUM(@guid1), chksum_guid2 = CHECKSUM(@guid2) Hash functions provide stronger guarantees against collision. The HashBytes function in SQL Server Denali provides data hashing and supports the following algorithms : MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512 | 
|
-
 Ever wanted to find out what was happening when SQL Server crashed? Your investigation should also include reviewing the SQL Server activities much like what profiler would do. SQL Server 2005 (including 2008, 2008R2 and the upcoming 2012) come with a default trace enabled out of the box. This trace keeps track of configuration changes, process level information and other information that can be very helpful for troubleshooting SQL Server related issues.
The default trace file can be opened and examined by launching SQL Server Profiler and by loading the log.trc file from (\Program Files\Microsoft SQL Server\<Instance_Name>\MSSQL\Log\) location or by querying it with Transact-SQL using the fn_trace_gettable system function. Alternatively, you can query the trace file using the following T-SQL statement:
SELECT * FROM fn_trace_gettable ('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc', default); -- Make sure you point to the right drive instead of C:\ drive. Note: By default this trace is on and can’t be stopped by using the SP_Trace_SetStatus system stored procedure. Instead, you will need to stop the default trace by using SP_Configure option, but it is not recommended (or a really good idea) to disable this tracing since the overhead is low and the data it provides can be a very handy troubleshooting tool. Some of the information provided by the default trace includes:
Configuration change history
Schema Changes History
Memory Consumption
All Blocking Transactions
Top Sessions
Top Queries by Average CPU time
Top Queries by Average IO, Etc.
This data can also be accessed from the Admin reports (from SQL Server Management Studio right-click the registered server and select "Reports") 
|
-
 Wouldn’t it be nice to have the ability to insert multiple rows of data using one Insert statement? Every time you create a table and have to insert rows manually, you will need to repeat “Insert Into <Tables> values..”
SQL Server 2008 and up provide the ability to insert multiple rows of data through one INSERT statement. This makes the entire statement part of one single DML (Data Manipulation Language) operation. This is called Table Value Constructor and it can be specified in the VALUES clause of the INSERT statement. LETS TAKE AN EXAMPLE: You want to create an ITEM table with ITEMNO and ITEMNAME and you would like to insert 5 records in it. | SAMPLE ITEM TABLE CREATION | | Create Table ITEM ( ITEMNO INT IDENTITY(1,1), ITEMNAME VARCHAR(50) ) | | TRADITIONAL INSERT STATEMENT FOR INSERTING MULTIPLE ROWS | | In earlier versions of SQL Server, you would have to repeat the insert statements for as many number of times you had to insert the rows. INSERT INTO ITEM VALUES ('MANGO') INSERT INTO ITEM VALUES ('APPLE') INSERT INTO ITEM VALUES ('BANANA') INSERT INTO ITEM VALUES ('GRAPES') INSERT INTO ITEM VALUES ('PLUMS') | | INSERTING ROWS WITH TABLE VALUE CONSTRUCTOR | | With the introduction to Table Value Constructor, you no longer have to use the insert statements multiple times. One Insert statement will do it. INSERT INTO ITEM VALUES ('MANGO'), ('APPLE'), ('BANANA'), ('GRAPES'), ('PLUMS') | As you can see from the above INSERT statement, you inserted multiple records using one single INSERT statement. Keep in mind: You can only insert up to 1000 records using Table Value Constructor. However, if you have more than 1000 records, you should look in to using BCP or BULK INSERT. BONUS TIP: GO BATCH REPLICATOR Did you know: The ‘GO’ statement is not a T-SQL statement? It is a command recognized ONLY by the client utilities like (SQLCMD, OSQL & SQL Server Management Studio, etc.) Whenever the client utilities encounter a ‘GO’ statement, the ‘GO’ signals the SQL Server engine that the command has reached the end of statement. The ‘GO’ command is NEVER sent to the SQL Server database engine. Also, any SQL Server user, regardless of permissions, can execute ‘GO’ statement using any SQL Server client utility. With the latest edition of SQL Server client tools, ‘GO’ now supports an additional parameter <count> that allows the batch to be executed for that many number of times. This means if you have an insert statement as follows: | INSERT INTO PRODUCTS VALUES (1, 'SQL SERVER 2008', 'MSSOLVE TEAM') GO | This will insert only one record to the PRODUCTS table. However, if you don’t care about data redundancy and want to insert the same row 10 times, you no longer need to execute the above statement 10 times. Instead, you will simply pass 10 as a parameter to GO command as shown in the below example: | INSERT INTO PRODUCTS VALUES (1, 'SQL SERVER 2008', 'MSSOLVE TEAM') GO 10 | The above ‘GO’ command will take care of inserting the same row 10 times in the PRODUCTS table. 
|
-
 In this third and final instalment of our series on the SQL Server Database Engine Permission Model, we look execution context switching – in other words when I access an object, from whose perspective should I be assigned permissions.
As always, if you want to test these out and don’t have SQL Server handy, you can download a full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try! SQL SERVER DATABASE ENGINE PERMISSION MODEL (PART 3)
The principal of least privilege requires that users be granted the most restrictive set of privileges required to perform tasks in order to limit the damages caused by security incidents. In the context of a database application, users must only be allowed to perform those operations required within the context of an application and nothing more. Restricting user permissions thus helps to limit the potential for inappropriate data access and database actions. By using the SQL Server permission model, database administrators can associate permissions directly to user objects or to groups. To be successful, however, database administrators must apply due diligence in setting up user accounts and should carefully provisioning groups and control access to objects by user/group entities. Stored procedures and functions are very common in modern database applications and quite often it is required for users to get access to application database modules (i.e. stored procedures and functions) without have additional permissions to access the objects referenced by the modules directly. In SQL Server, this pattern can be accomplished through ownership chaining, context switching and module signing. This article will focus on explaining about context switching in SQL Server. In the next article, we will explain about module signing in SQL Server. To refresh your understanding about ownership chaining, please review the ownership chaining article here. Execution Context Switching It’s very common to have multiple users owning various objects in the database. Quite often, you might want to give access of your object to another user. Context switching can be used when a module needs to be executed under the permission of a different user and was introduced in SQL Server 2005 to alleviate the administrative burden on the database administrator. 
Figure 1: Explaining execution context in SQL Server As illustrated in figure 1, when user ‘User A’ calls stored procedure ‘Proc2’ in UserB schema, the execution context of user ‘User A’ is switched to user ‘UserZ’ execution context. The procedure selects data from Table2 and since UserZ does not own Table2 permissions are checked for UserZ on Table2. Within functions and stored procedures, the EXECUTE AS clause supports the following qualifiers – (1) CALLER – causes the module to be executed in the context of the user executing the module. (2) SELF – causes the module to execute in the context of the user who created or last altered the module (3) OWNER – causes the module to execute under the context of the module owner. (4) ‘user name’ – causes the module to execute under the context of a given username. It is important to note that the IMPERSONATE permission is required for creating / altering any module that specifies the EXECUTE AS clause. The REVERT statement switches the execution context back to the caller of the last EXECUTE AS statement. | A T-SQL example illustrating context switching | | use master GO create database ExecutionContextDB GO create login BarneyLogin with password='1GoodPassword' create login FredLogin with password='2GoodPassword' create login WilmaLogin with password='3GoodPassword' GO use ExecutionContextDB GO --Create our database users mapped to their login and default schema create user BarneyUser for login BarneyLogin with default_schema=BarneySchema create user FredUser for login FredLogin with default_schema=FredSchema create user WilmaUser for login WilmaLogin with default_schema=WilmaSchema GO --Create our schemas for each user create schema BarneySchema authorization BarneyUser GO create schema FredSchema authorization FredUser GO create schema WilmaSchema authorization WilmaUser GO --Create a table that Barney's schema owns create table BarneySchema.RockHits ( YearPublished int NOT NULL, Title nvarchar(50) NOT NULL ) GO --Insert some data into the table insert into BarneySchema.RockHits values('1960','Pebbles Jam') insert into BarneySchema.RockHits values('1961','Dino Disco') insert into BarneySchema.RockHits values('1961','Fred''s Dance Formula') GO GRANT SELECT ON BarneySchema.RockHits to FredUser --Create the stored procedure that Fred's Schema owns --The stored proc executes under whomever is calling it using EXECUTE AS CALLER create procedure FredSchema.ListHits @Year int WITH EXECUTE AS CALLER AS BEGIN select CURRENT_USER as '(Execute as Caller), Current User Context=' select YearPublished,Title from BarneySchema.RockHits where YearPublished=@Year END --Let's grant Wilma the ability to execute this stored proc GRANT EXECUTE ON FredSchema.ListHits to WilmaUser --At this point we can begin playing with context switching --We have given Fred access to Barney's table of hits --We have given Wilma access to Fred's Stored Proc --Let’s begin by logging in as Fred and seeing if this stored proc works execute as user='FredUser' GO exec ListHits 1961 --go back to sysadmin REVERT GO EXECUTE AS user='WilmaUser' GO exec FredSchema.ListHits 1961 --We get the SELECT permission denied error as expected --because the stored proc is executing as WilmaUser --Now let's ALTER the stored procedure so that it will run under --its owner, Fred. REVERT GO ALTER PROCEDURE FredSchema.ListHits @Year int WITH EXECUTE AS OWNER AS BEGIN select CURRENT_USER as '(Execute as Owner), Current User Context=' select YearPublished,Title from BarneySchema.RockHits where YearPublished=@Year END GO --Now let's try Wilma again EXECUTE AS user='WilmaUser' GO exec FredSchema.ListHits 1961 --As you can see the current user context is FredUser! and we didn't --have to give Wilma explicit permissions to the underlying table in --Fred's stored proc. REVERT GO | 
|
-
 Last week we started the first of a three-part series on SQL Server database permissions and the permissions security model used by the database engine. Last week’s blog post covered the commands used in T-SQL to manage permissions, as well as the security scope within SQL Server. This week we look into how permissions behave they are applied at various points of an ownership chain, such as when a table is referenced in a view which is referenced in a stored procedure and so on. What are the actual permissions that get applied? Read on to find out.
As always, if you want to test these out and don’t have SQL Server handy, you can download a full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!
SQL SERVER DATABASE ENGINE PERMISSION MODEL (PART 2) Consider the following problem, how would you give someone access to parts of a table without giving them permissions on the table directly? Column level permissions or views can be used to project only the required columns; however, when a permission check is done, it is intuitive to check access to the view and the underlying base table. OWNERSHIP CHAINING In SQL Server, when an object is accessed through a chain, the owner of the object referenced and the calling object is checked. If both objects have the same owner, permissions on the referenced object is not evaluated. This is called ownership chaining. This idea is extended beyond just database objects to work across databases as well (called Cross-database ownership chaining). 
Figure 1: Illustrating ownership chaining and cross-database ownership chaining in SQL Server As illustrated in figure 1, when Alex queries view July2003 (owned by Mary), a permission check is done for view July2003. However, since view July2003 references view SalesXY (also owned by Mary), a permission check is not done. Similarly, moving down the permission chain, a permission check is not done for view InvoicesXZ. Since view InvoicesXZ references view AcctAgeXZ (owned by Sam), a permission check is done since there is a change in the object owners along the chain. Similarly, a permission check is done on the base table ExpenseXZ since the table is owned by Joe (different user than Sam). | THE 3 BASIC RULES OF OWNERSHIP CHAINING | | 1. If you have access to a given object, and then reference another object through it (such as a view that accesses a table) where both objects have the same owner, permissions on the second object are not checked. If you have access to the first item, it's assumed you'll have access to the second. 2. If you have access to a given object, and then reference another object through it, but the second object does not have the same owner, the permissions on the second object are checked against the retrieving user's permissions. If the user doesn't have permissions that allow access to that second object, he can't read it. 3. All of this is instigated in the security context of the user who invokes the original object. If the user doesn't have access to that original object, nothing happens (which is just standard security practice). | | WHAT YOU SHOULD KEEP IN MIND WHEN USING OWNERSHIP CHAINING | | 1. Ownership chaining bypasses permission checks completely, which means that it also bypasses DENIES. For example : If Joe denies Bob access to a table (owned by Joe) but grants him access to a view (owned by Joe) that queries that table, Bob will be able to query the table through the view since no permission check is done on the base table due to ownership chaining. 2. Un-intended ownership chaining can grant access incorrectly It's often that objects are created as SYSADMINS resulting in the owner as dbo. Soon, a lot of objects are owned by dbo and because of ownership chaining, unintended access to objects can occur. You should always be mindful of ownership chaining when you are designing your database. It is recommended not to enable cross database ownership chaining to prevent un-intended access. For this reason cross-database ownership chaining is turned off by default. For example : In figure 1, Alex can use view July2003 in Database 1 to query the ProjectionsXY table in Database 2 if cross-database ownership chaining is turned on. An alternative is using module signing as described here. 3. Schemas and ownership chaining Ownership chaining permits bypassing permissions made by one object to another and schemas provide a way of grouping objects together under a single owner (the owner of the schema as in SQL Server 2005 and higher). By default, schema-contained objects are owned by the schema owner. However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership. What this means, from a security perspective, is that we need to check at both the object and the schema level for ownership and consider ownership chaining from there. For schemas to be affective at separating access to objects, typically, they should be owned by different principals. Avoid all schemas in the database to be owned by dbo. | | | | | | A SIMPLE T-SQL EXAMPLE ILLUSTRATING OWNERSHIP CHAINING | | CREATE DATABASE OwnershipChainedDB GO USE OwnershipChainedDB GO /* Create users Mary and Alex */ CREATE USER Mary WITHOUT LOGIN GO CREATE USER Alex WITHOUT LOGIN GO /* Create a sensitive table and change owner to Mary */ CREATE TABLE SensitiveData ( IntegerData INT ) GO ALTER AUTHORIZATION ON SensitiveData TO Mary GO /* Create procedure and change owner to Mary */ CREATE PROCEDURE SelectSensitiveData AS BEGIN SET NOCOUNT ON SELECT * FROM dbo.SensitiveData END GO ALTER AUTHORIZATION ON SelectSensitiveData TO Mary GO /*Note that at this point Alex does not have any explicit grants on the SensitiveData table */ /*Now grant execute on the procedure to Alex*/ GRANT EXECUTE ON SelectSensitiveData TO Alex /*Note that Alex can query data from the SensitiveData table through the procedure due to ownership chaining */ execute as user='alex' exec SelectSensitiveData revert; | 
|
-
This week we start the first of a three-part series on SQL Server database permissions and the permissions security model used by the database engine. In conversations with many individuals across the country, I have found that not all DBAs or prospective DBAs that I have talked to are always sure about how SQL Server security works and a common understanding may help clarify some things. This week we start with the basics of understanding the three commands you use to configure SQL Server security and how they are applied in SQL Server. As always, if you want to test these out and don’t have SQL Server handy, you can download a full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!
SQL SERVER DATABASE ENGINE PERMISSION MODEL (PART 1) Imagine for a moment that you are a SQL Server production DBA. You arrive at the office and there are two new co-worker requests in your e-mail inbox: Bob works in merchandise and needs read-only access to all the sales data so that he can run reports to forecast purchase volumes, Alice is a new junior DBA who only requires access to the meta-data. These seem like easy requests to fulfill – or are they? The SQL Server Permission model can be used to solve this. Securables are entities that SQL Server controls access to through permissions. Permissions enable a principal to perform actions on a securable. Across all securable scopes, the primary commands to control access to a securable are GRANT, DENY and REVOKE. | UNDERSTANDING GRANT, DENY and REVOKE T-SQL COMMANDS | | GRANT, DENY and REVOKE are T-SQL commands for managing permissions. Although, you might have used them to control permissions, couple of times REVOKE and DENY have confused most of us. Here’s a brief explanation of GRANT, DENY AND REVOKE – (a) GRANT – Lets a principal perform an operation on a securable object. (b) DENY – Denies permission to perform an operation to a principal on a securable. Denies take precedence over all GRANT permissions and thus principals will not be allowed to perform the operation requested on the securable. (c) REVOKE – Removes the assigned GRANT/DENY permissions on a securable. | | Example - You can GRANT Bob EXECUTE permissions on a stored procedure but then realize you made a mistake. If you REVOKE the EXECUTE permission then you are simply reverting back to the state before the GRANT. Bob might still have access to the procedure through Windows group or SQL Server role memberships. If you DENY Bob EXECUTE permissions, then he will not be able to execute the stored procedure even if he receives the permission through another group or role membership. Additionally, in the case of fixed role memberships, DENYs do not take precedence. | | Securable scopes in SQL Server | | SQL Server securables contain three scopes, which are used to assign permissions to users. The securables are nested and each securable contains various other securables as shown in the figure below – (1) Server scope which includes server roles, logins etc. (2) Database scope which includes database users, application roles, database roles, etc. (3) Schema scope which includes various database objects such as tables, views, stored procedures, etc. 
Figure 1 : SQL Server Security Principals and Securables To look at the permissions that are assigned at the server scope, use the sys.server_permissions catalog view. To look at the permissions that are assigned at the database scope, use the sys.database_permissions catalog view. | | | | What permissions do I have? | | You can use the sys.fn_get_my_permissions function to get a list of permissions held by the calling principal on a specified securable. SELECT * FROM fn_my_permissions(NULL, 'SERVER');
SELECT * FROM fn_my_permissions (NULL, 'DATABASE'); This function only returns the permissions obtained by one of the following : (1) Permission directly granted to the principal and not denied. (2) Permission implied by a higher-level permission held by the principal and not denied. (3) Permission granted/held to a role or group of which the principal is a member of and not denied. | | How many permissions and how do they relate to each other? | | SQL Server Code-named ‘Denali’ has 214 permissions in total and we can’t cover every permission here. Look at the poster available here to learn about them and their relationship to each other. | | | | Back to solving the permission problem for Bob and Alice | | If we can identify the tables that Bob needs access to we can grant select permission to Bob to these tables. Alternatively, if there are many users such as Bob that might have similar data access requirements, we can create a database role (say MerchandiseReaders), assign the role required permissions and add users who need to access to read merchandise information into the MerchandiseReaders role. Since Alice only requires access to the meta-data, it is sufficient to grant Alice VIEW ANY DEFINITION and VIEW SERVER STATE permissions. | In the next two posts, we will go into details on topics related to ownership chaining, module signing and suggesting some best practices around SQL Server permissions such as least privilege.
|
-
OK, so I’ve been on a bit of a break over the last couple of months. Truth is, with TechDays Canada being one of the major things I am responsible for here at Microsoft Canada, time has been a previous resource. Now that the TechDays 2011 tour is complete, things are looking up to keep these blog posts going. This post provides you with some info on how to take advantage of SQL Server Management Studio (SSMS) features you may not be aware of. They work on all versions of SQL Server that I have tried (2008, 2008R2 and 2012 RC0) and provide a great way to create shortcuts to connect to specific server instances. To take them for a spin, you can download an full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!
SAVE TIME WITH ONE CLICK CONNECTION TO SQL SERVER You are a developer or DBA responsible for working with a specific database. Every time you open up SQL Server Management Studio, you have to provide the name of the SQL Server Instance, Authentication Type, User Name and Password and if you are planning on working with a specific database, you will need to click on Options and then select the default database. Once you are logged in, you will then need to click on New Query to open the query editor. Performing the above operations on a frequent basis to connect to SQL Server using management studio could time consuming. Wouldn’t it be nice if you could simply click on SQL Server Management Studio and it logs you in and also connects to the database you usually work with and open up query editor by default for you? You can now change the behavior of SQL Server Management Studio to make it work the way you want and here’s how you can do that. Please note that there are two changes that needs to be done: 1. Update the SSMS.EXE to include the connection parameters in the Shortcut link: | CONNECT TO SQL SERVER THRU MANAGEMENT STUDIO WITH DEFAULT VALUES | | Syntax: SSMS.EXE –S <ServerName> -d <Database_Name> -E Example: SSMS.EXE –S TK2SAMSQL01 –d MSSOLVE –E | You can update the shortcut link of SQL Server Management Studio from Start->Programs->SQL Server 2008-> SQL Server Management Studio link. (Simply right click on the link and select properties to update the link) 2. Configure SQL Server Management Studio to open Object Explorer and Query Editor by default: | OPEN QUERY EDITOR BY DEFAULT WHEN MANAGEMENT STUDIO IS LAUNCHED | | STEPS | ACTION | | 1 | SELECT TOOLS FROM SQL SERVER MANAGEMENT STUDIO MENU | | 2 | SELECT OPTIONS FROM THE TOOLS MENU | | 3 | SELECT GENERAL FROM THE ENVIRONMENT FOLDER | | 4 | CLICK ON THE DROP DOWN LIST OF “AT STARTUP” OPTION | | 5 | FROM THE DROP DOWN, SELECT “OPEN OBJECT EXPLORER AND NEW QUERY” | | 6 | CLICK ON OK AND CLOSE AND RESTART SQL SERVER MANAGEMENT STUDIO | After both the above changes are implemented, SQL Server Management Studio will need to be closed. When you launch SQL Server Management after making the above changes, it will bypass the security dialog box and will connect you straight to the database you want to work with and will also launch query editor along with object explorer. This could potentially save the time of a developer by not having to go thru multiple manual steps. | SQL SERVER MANAGEMENT STUDIO CAN OPEN UP 4 DIFFERENT TYPES OF WINDOWS AT STARTUP | | 1. OBJECT EXPLORER (This is the default window) 2. NEW QUERY WINDOW 3. OBJECT EXPLORER AND QUERY EDITOR (You should select this for this example) 4. EMPTY ENVIRONMENT | Important: You will need to close SQL Server Management studio and launch it again for the above changes to take effect. 
|
-
OK, so I’ve been on a bit of a break over the last couple of months. Truth is, with TechDays Canada being one of the major things I am responsible for here at Microsoft Canada, time has been a previous resource. Now that the TechDays 2011 tour is complete, things are looking up to keep these blog posts going. This post provides you with some info on how to take advantage of SQL Server Management Studio (SSMS) features you may not be aware of. They work on all versions of SQL Server that I have tried (2008, 2008R2 and 2012 RC0) and provide a great way to create shortcuts to connect to specific server instances. To take them for a spin, you can download an full-featured evaluation copy of SQL Server 2008 R2 from the TechNet Evaluation Center at http://technet.microsoft.com/en-ca/evalcenter/default.aspx (look under Server Products and Technologies | Release). If you are feeling adventurous and want to explore SQL Server 2012 RC0, you can download it from http://technet.microsoft.com/en-ca/evalcenter/hh225126.aspx. Give it a try!
SAVE TIME WITH ONE CLICK CONNECTION TO SQL SERVER You are a developer responsible for working with a specific database. Every time you open up SQL Server Management Studio, you have to provide the name of the SQL Server Instance, Authentication Type, User Name and Password and if you are planning on working with a specific database, you will need to click on Options and then select the default database. Once you are logged in, you will then need to click on New Query to open the query editor. Performing the above operations on a frequent basis to connect to SQL Server using management studio could time consuming. Wouldn’t it be nice if you could simply click on SQL Server Management Studio and it logs you in and also connects to the database you usually work with and open up query editor by default for you? You can now change the behavior of SQL Server Management Studio to make it work the way you want and here’s how you can do that. Please note that there are two changes that needs to be done: 1. Update the SSMS.EXE to include the connection parameters in the Shortcut link: | CONNECT TO SQL SERVER THRU MANAGEMENT STUDIO WITH DEFAULT VALUES | | Syntax: SSMS.EXE –S <ServerName> -d <Database_Name> -E Example: SSMS.EXE –S TK2SAMSQL01 –d MSSOLVE –E | You can update the shortcut link of SQL Server Management Studio from Start->Programs->SQL Server 2008-> SQL Server Management Studio link. (Simply right click on the link and select properties to update the link) 2. Configure SQL Server Management Studio to open Object Explorer and Query Editor by default: | OPEN QUERY EDITOR BY DEFAULT WHEN MANAGEMENT STUDIO IS LAUNCHED | | STEPS | ACTION | | 1 | SELECT TOOLS FROM SQL SERVER MANAGEMENT STUDIO MENU | | 2 | SELECT OPTIONS FROM THE TOOLS MENU | | 3 | SELECT GENERAL FROM THE ENVIRONMENT FOLDER | | 4 | CLICK ON THE DROP DOWN LIST OF “AT STARTUP” OPTION | | 5 | FROM THE DROP DOWN, SELECT “OPEN OBJECT EXPLORER AND NEW QUERY” | | 6 | CLICK ON OK AND CLOSE AND RESTART SQL SERVER MANAGEMENT STUDIO | After both the above changes are implemented, SQL Server Management Studio will need to be closed. When you launch SQL Server Management after making the above changes, it will bypass the security dialog box and will connect you straight to the database you want to work with and will also launch query editor along with object explorer. This could potentially save the time of a developer by not having to go thru multiple manual steps. | SQL SERVER MANAGEMENT STUDIO CAN OPEN UP 4 DIFFERENT TYPES OF WINDOWS AT STARTUP | | 1. OBJECT EXPLORER (This is the default window) 2. NEW QUERY WINDOW 3. OBJECT EXPLORER AND QUERY EDITOR (You should select this for this example) 4. EMPTY ENVIRONMENT | Important: You will need to close SQL Server Management studio and launch it again for the above changes to take effect. 
|
-
 SQL Server 2012 (previously SQL Server code-named “Denali”) has, so many great features for reporting, high availability, business intelligence, and more. If you haven’t had time to sit down and start learning about what’s new in the latest release of SQL Server, join Microsoft Canada for a weekly webcast series to help discover and understand the benefits of the latest release of SQL Server. Starting on November 9th and continuing every week until December 7th at 1:00 pm EST, you can join in on a series of webcasts all about SQL Server Denali.
Enterprise Information Management November 9, 2011, 1 – 2pm EST Presented by Darren King, Technical Specialist – Data Platform, Microsoft Canada Inc.
An overview and demonstration of the technology and a chance to see for yourself how SQL Server can empower your EIM strategy today BI Semantic Model November 16, 2011, 1 – 2pm EST
Presented by Howard Morgenstern, Technical Specialist – Business Intelligence, Microsoft Canada Inc.
Learn how organizations can scale from small personal BI solutions to the largest organizational BI needs with the BI Sematic Model. SQL Azure- Reporting Services November 23, 2011, 1 – 2pm EST
Presented by Richard Iwasa, Senior Consultant, Solution Architect, Ideaca
Learn how SQL Azure Database can help you reduce costs by integrating with existing toolsets and providing symmetry with on-premises and cloud databases. Discover what SQL Azure can do for you. Appliances November 30th, 2011, 1 – 2pm EST
Presented by Doug Harrison, Solution Specialist – Platform, Microsoft Canada Inc.
An overview of Microsoft's vision for appliances details on the workloads supported today and in the future. Mission Critical Confidence – Enable mission critical environments December 7th, 2011, 1 – 2pm EST
Presented by Marc Theoret, Technical Specialist – Data Platform, Microsoft Canada Inc.
Find out how to enable Mission Critical Environments (focusing on availability and performance) with manageable costs. As an added bonus, attendees that register and attend all five (5) modules of the webcast will receive a SQL Server Code Name "Denali" USB key and a copy of Windows Azure Step by Step (May 2011) to be mailed 4-6 weeks after the final webcast. Limit one per person. Attend the webcasts, download the Denali evaluation and get yourself up and running with SQL Server Denali. Register today! 
|
-
A few days ago I posted the resources for the TechDays 2011 Communications and Collaboration track. This post is intended to provide those who attended session in the Data Platform track to be able to continue their learning with the resources listed for each of the sessions delivered at TechDays 2011 in Toronto, as well as overall resources for the track. As was evident from the content, a large focus of the Data Platform track at TechDays 2011 was business intelligence, as well as SQL Server 2012 (formerly SQL Server code-named “Denali”). Sessions also dealt with topics such as some of the T-SQL coding options in SQL Server 2008R2 and SQL Server 2012, as well as techniques to migrate to SQL Server 2008 R2 and SQL Server 2012. Hopefully you left each session understanding all the concepts and knowing exactly what you needed to do in order to take advantage of the great features of SQL Server in your organization. Below you’ll find all of the resources mentioned in each of the sessions so that you can continue your learning today (rather than having to wait until the sessions are available on TechDays Online in early December): Highlighted in all sessions: Resources for individual sessions DAT274 - A Lap around Microsoft Business Intelligence - Part 1 
DAT275 - A Lap around Microsoft Business Intelligence - Part 2 DAT279 - New Business Intelligence Functionality in Microsoft SQL Server 2012 DAT302 - Introducing the Next Generation High Availability Solution: Microsoft SQL Server 2012 AlwaysOn DAT304 - What's New in Manageability for Microsoft SQL Server 2012 DAT318 - The T-SQL Cookbook: What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server 2012
DAT319 - Upgrading to Microsoft SQL Server 2008 R2 and SQL Server 2012: A Comprehensive Look
Looking ahead to TechDays Vancouver Stay tuned to next week’s post where I’ll go over what is in store for you in the Data Platform track at TechDays Vancouver! If you haven’t yet registered for TechDays Vancouver (or Montreal, for that matter), there’s still time to register. Register today >> Make sure to check out the TechDays LinkedIn group. TechDays Facebook page, and/or the TechDays Twitter feed to join the conversations and get the latest updates. 
|
-
We’ve revealed the keynotes, the Windows Client and Phone Track, the Developer Tools, Languages and Framework track, the Developer Practices & Architecture, and the Cloud Computing and Online Services track, but the one thing that all of these have in common is the need to access, store, and manipulate data. Organizations rely on IT to help ensure that corporate data is safe and secure allowing business continuity. The ability to gain insight from the mountains of data most organizations possess is also a key requirement for most IT departments. You will learn how to take advantage of the capabilities of current and upcoming releases of SQL Server, to help provide a secure well-managed data platform that easily extends to the Cloud to help enable unparalleled scalability for your organization. You will also learn how to take advantage of business intelligence tools built into SQL Server, as well as offered as an appliance, to help provide the key business insight that can allow an enterprise to make the right business decision with a solid data-based foundation. Developers will learn how to take advantage of the Microsoft Data Platform to create compelling business applications taking advantage of the capabilities of the current and upcoming releases of SQL Server. If you don’t believe me, ask Susan Ibach. If that’s not enough, keep reading to discover some of the sessions you can check out this year at TechDays. DAT271 – Microsoft SQL Server Code-Named “Denali: Ramping up for RTM In this session we will explore some key tangible benefits of Microsoft SQL Server Code-Named “Denali” from the perspective of the developers and the DBA. This is not a marketing campaign for your boss; it’s for YOU to understand what you can deliver with the upcoming version of SQL Server.
DAT274 - A Lap around Microsoft Business Intelligence (Part 1) If you are familiar with Microsoft SQL Server, Microsoft Office or Microsoft SharePoint Server, but you have not had the chance to explore the broad and integrated Business Intelligence (BI) capabilities offered by across these products, then this two-part presentation will guide you through an end-to-end walkthrough of how these products come together to provide a complete platform to build powerful BI solutions. The focus of Part 1 will be the core BI concepts, SQL Server Reporting Services and PerformancePoint Services.
DAT275 - A Lap around Microsoft Business Intelligence (Part 2) In part 2, we will continue to review other Business Intelligence (BI) capabilities offered by Microsoft SQL Server, Microsoft Office and Microsoft SharePoint Server. You will learn about Excel Services, and PowerPivot V2 with the inclusion of Microsoft SQL Server code-named “Denali”. Finally, we will discuss the architecture viewpoint of how these products should be used to provide a robust platform that can help proactive decision making and business performance monitoring.
DAT279 - New Business Intelligence Functionality in Microsoft SQL Server Code-Named "Denali" In this session we dive into some of the new Business Intelligence capabilities that are being introduced in SQL Server code-named "Denali". We’ll start with the new Business Intelligence Semantic Model (BISM) and shown how BI developer scenarios are enhanced in Visual Studio 2010 and deployed to the new Tabular version of Analysis Services. You will then learn about the new self-service BI scenarios leveraging the BISM along with Project Crescent, a new interactive web based authoring and data visualization tool that is tightly integrated into SharePoint 2010.
DAT302 - Introducing the Next Generation High Availability Solution: Microsoft SQL Server Code-Named "Denali" AlwaysOn In this session we talk about the new high availability solution that is being introduced in SQL Server code-named "Denali". The session provides an overview of AlwaysOn and introduces the key new features and capabilities that will help businesses achieve the high availability SLA for mission critical applications. The session is the first part of a three-part series. It is highly recommended that you attend both the sessions to get a complete understanding of how your business can benefit from AlwaysOn. - Session Lead: Richard Baumet
- Cities: Toronto, Vancouver, Montreal
- Technologies: SQL Server “Denali”
DAT304 - What's New in Manageability for Microsoft SQL Server Code-Named "Denali" Microsoft SQL Server "Denali" has many new features and enhancements. With a focus on manageability, this session tours new features including Contained Databases, Distributed Replay Utility, User Defined Roles, SQL Management Studio enhancements, and PowerShell. The session is demo-intensive and is designed to provide clear guidance and help you define a strategy to implement the upcoming release of SQL Server. - Session Lead: Kevin Harmer
- Cities: Toronto, Vancouver, Montreal
- Technologies: SQL Server “Denali”
DAT318 - The T-SQL Cookbook: What's Cool in Microsoft SQL Server 2008 R2 and New in SQL Server Code-Named “Denali” In this session you will learn about the new T-SQL-related features that are being delivered in the next version of SQL Server code-named “Denali” as well as review some of the cool features that already exist in SQL Server 2008R2 but you may not yet be using. We show off some cool demos as well as outline when these language features can be used to help you solve various coding issues.
DAT319 - Upgrading to Microsoft SQL Server 2008 R2 and SQL Server Code-Named "Denali": A Comprehensive Look This session provides an in-depth look at how to upgrade to SQL Server 2008 R2 or how to upgrade to the next major release of SQL Server called SQL Server "Denali". The session covers the essential phases and steps involved in upgrading from SQL Server 2000, SQL Server 2005, SQL Server 2008 to SQL Server 2008 R2 or SQL Server "Denali" by using best practices and available resources. We cover the complete upgrade cycle, including the preparation tasks, upgrade tasks and post-upgrade tasks. This session covers upgrading a stand-alone instance, upgrading a clustered instance, upgrading instances involved in mirroring, log shipping, and replication, feature-specific considerations and recommended tools for a successful upgrade. Several demos are given covering the process and the available tools. The new feature of SQL Server "Denali" called “Product Update”, the next version of SQL Server slipstream is also covered showing you how to discover critical updates that you should include when installing or upgrading to SQL Server "Denali".
Two days of fantastic content to help you do your job more effectively! Come to TechDays, listen, ask questions, learn from the experts! Register today and join us in Toronto October 25,26th, Vancouver November 15,16th, or Montreal November 29,30th. If you register now you can take advantage of the EarlyBird discounts. Get your ticket today before the Early Bird pricing ends in your city: - Toronto - Oct 11th 2011
- Vancouver - Oct 25th 2011
- Montreal - Nov 8th 2011
|
-
 Continuing on a topic that includes some more content aimed at DBAs and developers, this week we’ll look at how SYNONYMs can make your life easier.
What would you do when an object that is referenced in several places and in several databases (Stored Procedures, Functions, etc.) is being moved to a different database or is being renamed due to a new object naming policy? Think of how much work this change would generate for you? Wouldn’t it be nice if there was a way to take care of these changes automatically? Friends, let’s welcome Synonym to the SQL Server features family. A synonym is an alternative name (think of it like an alias) for objects in SQL Server that provides a single-part name to reference a base object that uses long object names, a two-part, three-part, or even for-part names object names. Synonyms can reference objects that exist on local or remote servers. They provide a layer of abstraction to protect client application from any changes made to the name or the location of the base underlying object. The binding between a synonym and its underlying object is strictly by name only which means the underlying object can be modified, dropped or dropped and replaced by another object. You can also perform any DML operations on a synonym which actually gets performed on the underlying table. Synonyms can be very helpful in the above scenario. Once you create synonyms for objects, you can move or rename your base object without affecting its reference or use. Synonyms can also be used for creating short-cuts for long object names or the object names used in four-part queries for example linked servers. (ex: ServerName.DatabaseName.OwnerName.ObjectName) SYNONYM can be created on the following object types:
· User Tables (permanent and temporary)
· Views
· Stored procedures (TSQL & CLR)
· Extended Stored Procedures
· Replication Filter Procedures
· Assembly Aggregate Functions (CLR)
· Assembly Table Valued Functions (CLR)
· Scalar Functions · Inline Table Valued Function, etc. Let’s now look at some of the examples on how to use synonyms in SQL Server. | CREATING A SYNONYM FOR LOCAL OBJECT | | CREATE SYNONYM CUST_ADD FOR ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS; GO --QUERYING THIS SYNONYM SELECT * FROM [CUST_ADD] GO --DROPPING THIS SYNONYM DROP SYNONYM [CUST_ADD] GO | | CREATING A SYNONYM FOR LINKED SERVER OBJECT | | CREATE SYNONYM CUST_ADD FOR SQLTIPS.ADVENTUREWORKSLT2008R2.SALESLT.ADDRESS; GO --QUERYING THIS SYNONYM SELECT * FROM [CUST_ADD] GO --DROPPING THIS SYNONYM DROP SYNONYM [CUST_ADD] GO | | INSERTING A ROW USING A SYNONYM | | INSERT INTO [CUST_ADD] VALUES ('ONE WAY' ,'MICROSOFT WAY' ,'REDMOND' ,'WASHINGTON' ,'USA' ,98052 ,NEWID() ,GETDATE()) GO | 
|
|
|
|