I trust that all you are finding my tutorial series on the SQL Server Profiler useful. Recently, I received an interesting question related to assigning permissions to a SQL Server user to allow them to use the SQL Server Profiler.

Today, using a simple experiment, I will be demonstrating the following:

1. What minimal permissions are required for a user to capture SQL traces using the SQL Server profiler?
   
2. How to assign these minimum permissions to a SQL Server user?

The Demo

To begin with establishing an environment for the experiment, I will first create a SQL Server login and a user associated to that login.

–Creating the logins/users

USE master

GO

CREATE LOGIN ProfilerTest01

WITH PASSWORD = ‘Passwd@12’, CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF, DEFAULT_DATABASE = AdventureWorks2008R2

GO

USE AdventureWorks2008R2

GO

CREATE USER ProfilerTest01

FOR LOGIN ProfilerTest01

GO

Normal logins/users cannot run the Profiler

I will now launch the SQL Server Profiler and attempt to login using this newly created login/user – ProfilerTest01. The error message that I receive is:

Looking at the message again indicates that in order to allow this user to use the SQL Server Profiler, I have a choice in either providing the user sysadmin fixed server role access (not advised) or grant the ALTER TRACE permission.

In order to run a trace against SQL Server you must be a member of sysadmin fixed server role or have the ALTER TRACE permission.

Not Recommended: Providing sysadmin fixed server role access is not recommended because it would grant the login/user complete control over the entire SQL Server instance and therefore does not go with the “minimum permissions” paradigm.

The only choice therefore is to provide the user ALTER TRACE permissions.

Solution – Assign the ALTER TRACE permissions

The solution therefore is to assign ALTER TRACE permissions to the login using the following GRANT statement. The ALTER TRACE permission is a server-scoped permission being granted to a server-level principal (a login) which means that granting somebody this permission will expose the entire SQL Server instance for the mentioned purpose (in this case, tracing).

–Granting permissions to the logins/users

USE master

GO

GRANT ALTER TRACE TO ProfilerTest01

GO

It is important here to switch back to the master database because the permission that we are attempting to grant is a server scoped permission and therefore not being in the master database can result in the following error:

Msg 4621, Level 16, State 10, Line 1

Permissions at the server scope can only be granted when the current database is master

Now that I have executed the script to GRANT the ALTER TRACE permission, I will now attempt to use the SQL Server Profiler with this login. I can see that the login succeeded and the Profiler brought the trace properties window.

After configuring the trace (Part 02-Customizing Profiler Templates), I execute the following test statements below while the Profile trace is running. The test statements are random queries for demonstration only.

–Test Workload (Login to the Profiler with the test login before executing the workload)

USE AdventureWorks2008R2

GO

SELECT * FROM HumanResources.Employee

GO

USE master

GO

SELECT * FROM sys.configurations

GO
USE msdb

GO

SELECT * FROM sys.systypes

GO
USE AdventureWorksLT

GO

SELECT * FROM BuildVersion

GO

SECURITY WARNING! The key point to note here is that I am executing queries against multiple databases in the system. The login running the trace does not have permissions to the “AdventureWorksLT” database, yet queries executed against that database can be traced by the user in the Profiler. This is because the ALTER TRACE is a server-scoped permission as mentioned above.

SQL Logins v/s Windows Logins

While the experiment above was demonstrated using SQL Logins, the process remains the same if SQL logins mapped to a Windows logins are to be used. I trust that these steps will help you grant Profiler permissions to your development team without having to make them sysadmins on your SQL Server instances.

References:

• Profiler Permissions : http://msdn.microsoft.com/en-us/library/ms187611.aspx
  
• GRANT statement : http://msdn.microsoft.com/en-us/library/ms186717.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Storage, although cheap is still a limited resource. In our development and sustenance environments, we often get databases from multiple different sources which we need to use to troubleshoot certain issues or carry out some research and/or other development activity on. The backups of these databases take up considerable amount of space and we ended up either using 3rd party tools, or ensuring that the DBA uses a file compression tool to reduce the disk space consumption. When SQL Server 2008 was released, it’s backup compression feature came to our rescue.

Backup Compression (Single-use)

• Starting SQL Server 2008 Enterprise Edition and above, Microsoft SQL Server has the ability to perform a “compressed backup”
  
  
  
  • Since Developer Edition contains the full feature set of the Enterprise Edition, it too can perform compressed backups
  
  
• Every edition of SQL Server 2008 and later can restore a compressed backup
  
• Compressing a backup typically requires less device I/O and therefore usually increases backup speed significantly
  
  
  
  • Please note the using backup compression increases CPU usage

Specify backup compression using the SSMS UI

When backing up the database, we can choose to compress the backup in the “Options” tab of the Backup database wizard.

Specify backup compression using the T-SQL

The backup database wizard is great, however, is not used  frequently in environments. Most of the production, staging and quality assurance environments use T-SQL statements to backup a database. Compression can be specified in the BACKUP DATABASE statement as shown below:

BACKUP DATABASE [AdventureWorks2012]

TO  DISK = N’C:DataMSSQL11.SQL2K12MSSQLBackupAdventureWorks2012.bak’

WITH NOFORMAT, NOINIT,

NAME = N’AdventureWorks2012-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

Setting Backup Compression by Default

Instead of remembering to choose the backup compression, an alternative arrangement can be done wherein backup compression is always enabled by default on the given SQL Server instance. This can be done in two ways:

Backup Compression by Default setting using the SSMS UI

Within the Object Explorer, we launched the Server Instance Properties window by right-clicking on the SQL Server instance name and choosing “Properties”. When we navigate over to the “Database Settings” page, we find a switch – “Compress backup”. When checked (and the SQL Server contains an Enterprise Edition feature set), all backups using server default setting will be compressed backups.

Backup Compression by Default setting using T-SQL

Because backup compression is a SQL Server instance level configuration setting, the only way we can use T-SQL to set backup compression by default is to use the system stored procedure – sp_configure.

sp_configure ‘backup compression default’, 1

RECONFIGURE

GO

Setting the option ‘backup compression default’ to 1 will cause future backups from the server to be compressed, unless otherwise explicitly stated in the BACKUP DATABASE command.

References:

• Backup Compression: http://technet.microsoft.com/en-us/library/bb964719.aspx
  
• Backup Compression (Default) option: http://msdn.microsoft.com/en-us/library/bb677250(v=sql.105).aspx

Until we meet next time,

Be courteous. Drive responsibly.

When documenting a database, one of the first things that is done is to document the list of tables in the database and the primary and foreign keys to establish a relationship between them.

As I was gathering my thoughts one evening, I realized that I knew 7 different methods to identify primary and 6 different methods to identify foreign keys within a database (Cool!). Today, I will share these methods with you.

The methods to identify (Primary & Foreign) Keys

1. Using the system stored procedure sp_help to get the data definition information for any given table
   
   
   
   • The only issue I have with this method is that it brings back a lot of additional information
   
   
2. Use the catalog view (specifically, object catalog view): sys.key_constraints
   
   
   
   • For foreign keys, one needs to use a combination of sys.foreign_key_columns and sys.columns
   
   
3. Use the catalog view (specifically, object catalog view): sys.indexes
   
   
   
   • The reason this works is because defining a set of primary keys would, unless explicitly specified otherwise, create a clustered index on those columns
     
   • This method my not be valid for foreign keys as one may not have defined indexes on these columns
   
   
4. Use the catalog view: sys.objects
   
   
   
   • Ultimately, a key is an object, and hence, an entry for the key has to exist in the sys.objects catalog view
   
   
5. Query the INFORMATION_SCHEMA
   
   
   
   • Querying the INFORMATION_SCHEMA is recommended in environments where Microsoft SQL Server is not the only RDBMS system in the mix
     
   • Reason: INFORMATION_SCHEMA views are ISO compliant
   
   
6. Use the system stored procedure: sys.sp_pkeys or sys.sp_fkeys
   
7. Use the system stored procedure: sys.sp_primarykeys or sys.sp_foreignkeys
   
   
   
   • This method is only applicable for linked servers

7 methods to identify Primary Keys

Following is the set of scripts highlighting the 7 methods to identify Primary Keys for the table: HumanResources.Employee within the AdventureWorks2012 sample database:

--Method 01 - Get the entire table definition
USE AdventureWorks2012
GO

--Method 01 - Get the entire table definition
EXEC sp_help [HumanResources.Employee]
GO

--Method 02 - Query the catalog views (resource database)
SELECT * 
FROM sys.key_constraints AS kc 
WHERE kc.type = 'PK' 
  AND kc.is_ms_shipped = 0 
  AND kc.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 03 - Query the catalog views (resource database)
SELECT * 
FROM sys.indexes AS si 
WHERE si.type = 1 
  AND si.index_id = 1 
  AND si.is_primary_key = 1 
  AND si.object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 04 - Query the catalog views (resource database)
SELECT * 
FROM sys.objects AS so 
WHERE so.type = 'PK' 
  AND so.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 05 - Query the INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_CATALOG = DB_NAME() 
  AND CONSTRAINT_SCHEMA = 'HumanResources' 
  AND TABLE_CATALOG = DB_NAME() 
  AND TABLE_SCHEMA = 'HumanResources' 
  AND TABLE_NAME = 'Employee'
GO

--Method 06 - Use a system stored procedure
EXEC sys.sp_pkeys @table_name = 'Employee', 
                  @table_owner = 'HumanResources'
GO

----Method 07 - Works only on linked servers
--EXEC sys.sp_primarykeys @table_server = N'WIN2k8SQL2K12', 
--                        @table_name = N'Employee',
--                        @table_catalog = N'AdventureWorks2012', 
--                        @table_schema = N'HumanResources'
GO

6 methods to identify Foreign Keys

Following is the set of scripts highlighting the 6 methods to identify Primary Keys for the table: HumanResources.Employee within the AdventureWorks2012 sample database:

USE AdventureWorks2012
GO

--Method 01 - Get the entire table definition
EXEC sp_help [HumanResources.Employee]
GO

--Method 02 - Query the catalog views (resource database)
SELECT OBJECT_NAME(Fkc.constraint_object_id) AS KeyName,
       Fkc.constraint_object_id,
       Fkc.constraint_column_id,
       Fkc.parent_object_id,
       Fkc.parent_column_id,
       Fkc.referenced_object_id,
       Fkc.referenced_column_id,
       sc.name
FROM sys.foreign_key_columns AS Fkc 
INNER JOIN sys.columns AS sc ON Fkc.parent_object_id = sc.object_id
                            AND Fkc.constraint_column_id = sc.column_id
WHERE Fkc.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 03 - Query the catalog views (resource database)
SELECT * 
FROM sys.objects AS so 
WHERE so.type = 'F' 
  AND so.parent_object_id = OBJECT_ID('HumanResources.Employee')
GO

--Method 04 - Query the INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_CATALOG = DB_NAME() 
  AND CONSTRAINT_SCHEMA = 'HumanResources' 
  AND TABLE_CATALOG = DB_NAME() 
  AND TABLE_SCHEMA = 'HumanResources' 
  AND TABLE_NAME = 'Employee'
GO

--Method 05 - Use a system stored procedure
EXEC sys.sp_fkeys @pktable_name = 'Employee', 
                  @pktable_owner = 'HumanResources'
GO

----Method 06 - Works only on linked servers
--EXEC sys.sp_foreignkeys @table_server = N'WIN2k8SQL2K12', 
--                        @pktab_name = N'Employee',
--                        @pktab_catalog = N'AdventureWorks2012', 
--                        @pktab_schema = N'HumanResources'
--GO

Which method is recommended by Microsoft?

All the methods shown above are still valid (i.e. not marked as “deprecated” in SQL Server 2012). To the best of my knowledge, while there is no official documentation which explicitly states whether method A is better than method B; the KB195526 – How to Retrieve Primary Keys for SQL Server Tables recommends usage of the system stored procedure: sp_pkeys.

Which one do you use?

I would like to know which method is most used by you. Also, if there are any other methods that you use, and would like to share for the benefit of all, please do so in the comments.

References:

• KB195526 – How to Retrieve Primary Keys for SQL Server Tables
  
• System Stored Procedures:
  
  
  
  • sp_help
    
  • sp_pkeys
    
  • sp_fkeys
    
  • sp_primarykeys
    
  • sp_foreignkeys
  
  
• Catalog Views:
  
  
  
  • sys.key_constraints
    
  • sys.foreign_key_columns
    
  • sys.columns 
    
  • sys.indexes
    
  • sys.objects
  
  
• INFORMATION_SCHEMA views

Until we meet next time,

Be courteous. Drive responsibly.

Have you ever been in a situation wherein you have been working furiously on your SQL Server Management Studio (SSMS) on multiple query windows and you need to rush out to a meeting? As you know, each query editor window within the SSMS opens a new connection to the SQL Server. Open connections mean more resources being consumed at the server. Hence, when going into a meeting or lunch, one may not want to keep these connections open. I don’t.

So, what do I do in such cases?

The answer is simple: I use the “Disconnect all queries” feature available in the SSMS query editor right-click context menu. Here’s a short demo.

As you can see in the screen-shot below, I have 4 queries open – each connected to a different SQL server instance (to cycle through the open queries, simply use the Ctrl+TAB keyboard shortcut).

Now, to disconnect all queries from the corresponding SQL Server instances in one go, right-click in the query editor window, go to Connections->Disconnect all queries.

Upon clicking “Disconnect all Queries”, notice that the tabbed document spaces for all queries are now reading “not connected”.

I personally don’t like to reopen the SSMS often – it’s launched once in the morning, and the only time I close it is when I shut down my laptop in the evening.  Having a “disconnect all queries” is a cool little feature that comes in very handy.

Hope you like it as much as I do! Do you know of any cool features within SSMS? Do let me know!

Until we meet next time,

Be courteous. Drive responsibly.

SQL Server 2012 has been around for quite a while now, and yet, I keep finding hidden enhancements. Now, the SSMS for SQL 2012 uses the VS2010 shell and therefore inherits a lot of it’s functionalities from there. Recently, I stumbled into the “Navigate To” window in the SSMS for SQL 2012 (“Denali”).

I keep all of my scripts (which I have used in the blogs or those that I use during work) under various projects within a solution. Over time as my collection grows, it becomes difficult to remember which script is located under which project, what is the name of the script, etc. In such cases, the “Navigate To” feature can used to quickly find a file within a solution in SSMS.

Some time ago, I shared three (3) scripts related to database backups and backup history on the Scripts Module of this site, BeyondRelational.com. You can quickly access these 3 scripts using the Search Module. Recently, I had to refer one of these scripts and here’s how I used the “Navigate To” window:

With the required SSMS solution open, launch the “Navigate To” window by going to the Edit menu –> Navigate To or use the keyboard shortcut [Ctrl+ ,]

Type a part of the File Name in the “Navigate To” window, and it immediately gets the list of files containing the search string.

Double-clicking the required file opens the file from the solution in the SSMS Query editor window.

So simple – I was able to reach to the required script in just a matter of seconds.

To all the DBAs out there – I find storing my scripts as solutions a very easy way to manage them – I can also share the entire solution with my colleagues via the source control system. If you have not experimented with solutions, I strongly suggest that you give it a try.

Until we meet next time,

Be courteous. Drive responsibly.