:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • Mar 04 / 2009
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions, T-SQL Scripts

Get permissions for SQL Server Database users

As a part of best practices implementation, some times i require to review and audit the rights of database users in a specific database. I have created following script to get list of permissions for all database users in a database. This script will work for SQL Server 2005 and onwards. I will run it on AdventureWorks

 
Use AdventureWorks  
 GO  
 SELECT USER_NAME(grantee_principal_id) AS [UserName],  
 OBJECT_NAME(major_id) AS ObjectName,  
 permission_name AS PermissionName,  
 state_desc AS PermissionStatus  
 FROM SYS.DATABASE_PERMISSIONS  
 WHERE class = 1  
 ORDER BY userName,permissionstatus  
 GO  

The result will be produced as shown a part in following snap.

Permissions for DB Users

It is important to mention that in case of permissions Column-level permissions override object-level permissions on the same entity.

  • Feb 02 / 2009
  • 0
dbDigger, Monitoring and Analysis, SSMS tips and tricks

How to generate Instance level reports through SQL Server Management Studio (SSMS)

Microsoft has introduced SQL Server Management Studio (SSMS) with SQL Server 2005 release. It is now a favorite tool of SQL Server DBAs. Most of new DBAs and students that are new to SQL Server feel a bit confusion to adopt various facilities for tasks provided in SSMS. I have sketched a step by step configuration for some basic tasks through SSMS.
In this post we will see how to generate Instance level reports through SQL Server Management Studio (SSMS). Follow these steps in your SSMS

Generate Server Instance Level Reports

From here you may choose required report.

  • Dec 11 / 2008
  • 0
dbDigger, Monitoring and Analysis, SQL Server tools

Duration value in SQL Server 2005 trace

Question: You make a SQL Trace in SQL Server 2005 and after you have loaded everything into a table you do a select and see the following results:

Duration Reads Writes CPU
1061610 400 0 66

When you look at the duration, what is the value here?

Answer: 1.06161 seconds

Explanation: The answer is 1.06161 seconds.

In SQL Server 2005, the server reports the duration of an event in microseconds (one millionth, or 10-6, of a second) and the amount of CPU time used by the event in milliseconds (one thousandth, or 10-3, of a second). In SQL Server 2000, the server reported both duration and CPU time in milliseconds. In SQL Server 2005, the SQL Server Profiler graphical user interface displays the Duration column in milliseconds by default, but when a trace is saved to either a file or a database table, the Duration column value is written in microseconds.

Consult us to explore the Databases. Contact us