Search Stored Procedure Code

Came across an interesting situation. The database that I was working with was missing from CVS, and I needed to be able to check what Stored Procedures were using Temporary tables. Thankfully I found a blog by one of my favorite authors which saved the day.

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text
http://blog.sqlauthority.com/2007/09/03/sql-server-2005-search-stored-procedure-code-search-stored-procedure-text/

TSQL to list all Permissions within a Database

Came across a situation today while trouble shooting a problem with replication where the subscription database has continually been getting modified by an unknown source and thus breaking replication. To assist with investigating this issue there was a need to look at all the permissions assigned to all users and roles within the database both at the Publisher and the Subscriber..... so here is the TSQL I wrote to help with this.

select sys.database_permissions.class_desc
, sys.database_permissions.type
, sys.database_permissions.permission_name
, sys.database_permissions.state
, sys.database_permissions.state_desc
, sys.database_principals.name as [grantee_principal_name]
, ISNULL(sys.schemas.name, '') as [schema_name]
, ISNULL(AllObjects.name, '') as [object_name]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'GRANT ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'GRANT ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Grant_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'DENY ' + sys.database_permissions.permission_name + ' TO [' + sys.database_principals.name + '];'
ELSE 'DENY ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] TO [' + sys.database_principals.name + '];'
END) as [Deny_Perms_Stmnt]
, (CASE
WHEN sys.database_permissions.class = 0 THEN 'REVOKE ' + sys.database_permissions.permission_name + ' FROM [' + sys.database_principals.name + '];'
ELSE 'REVOKE ' + sys.database_permissions.permission_name + ' ON [' + sys.schemas.name + '].[' + AllObjects.name + '] FROM [' + sys.database_principals.name + '];'
END) as [Revoke_Perms_Stmnt]
FROM sys.database_permissions
INNER JOIN sys.database_principals ON sys.database_principals.principal_id = sys.database_permissions.grantee_principal_id
--AND sys.database_principals.name = 'public' --uncomment this line to restrict the output to a single principal
LEFT JOIN (
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.objects
UNION
SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published
FROM sys.system_objects
) AllObjects ON AllObjects.object_id = sys.database_permissions.major_id
LEFT JOIN sys.schemas ON sys.schemas.schema_id = AllObjects.schema_id
ORDER BY sys.database_principals.name
, sys.database_permissions.class
, sys.schemas.name
, AllObjects.name

Infrastructure Saturday - Wrap Up

Firstly a huge thank-you to everyone that attended the session on Saturday, and also a thank-you to BenF for assisting with the presentation (and letting me out of the cage for the day).

I am working with the organizers of Infrastructure Saturday to make the slides available for download, so stay tuned for more information on that front.

Some interesting questions were raised during the session and I thought I would provide some additional information here for those that were interested.

During the session some questions/discussions were raised about Virtalisation. I won't go into this in this post because I am actually expecting some interesting news on that front to come out of the SQLPASS which is being held this week in Seattle which although I am not attending I will be following closely.

At the end of the session I was approached with the question "We currently have multiple SQL 2005/2008 servers but our monitoring is only of the server layer, can you suggest/recommend how we could monitor the SQL layer".

In my experience a combination of monitoring solutions can offer a much more holistic view of your environment. This also helps when monitoring multiple different Operating Systems (e.g. Windows and Linux). Of cause this is based on my experience with monitoring 220+ servers.

The reason a combination of monitoring solutions can offer more depth is because some of monitoring platforms offer better network monitoring than others, and other monitoring platforms offer better Application / Software monitoring.

The challenge with monitoring a SQL Server is that you not only need to monitor the Infrastructure (e.g. ICMP, TCP Port tests, etc) but you need to be able to check Event Logs and that Services are running. Most monitoring systems can provide those tests, however you also need to monitor the actual SQL Instance layer and some of the best ways of doing that is through some form of script which can run a TSQL statement or use SMO to check aspects of the environment and return a status code to the Monitoring environment.

Some of the key SQL layer aspects that a good monitoring solution should be able to check are:
- SQL Server Memory
- SQL Server Error Log
- SQL Server Settings
- Database File Free Space
- Database Status
- Database Settings (for enforcing environment settings)
- Blocking Locks

My experience has been largely about using Microsoft Operations Manager (aka MOM) and custom VBScripts to ensure that our business rules are enforced. This has also allowed a number of automated actions to be implemented while allowing for the dynamics of the farm.

Here are some links to some monitoring platforms to look at:

Microsoft System Center Operations Manager (aka SCOM, the latest release of MOM)
http://www.microsoft.com/systemcenter/en/us/operations-manager.aspx
If I had to recommend an enterprise software it would be SCOM, only because that is where my experience has been with. Of cause there are other offerings from EMC and other vendors which might be more suitable to your environment.

If your looking for some free monitoring solutions purhaps the following might be of interest:

Zenoss
http://www.zenoss.com/ or http://community.zenoss.org
I have had some experience with using this and it can offer a good monitoring tool for network monitoring (ICMP) and auto discovery. It does take some amount of tweaking to get to work well with Windows (as with any Unix based monitoring tool) however it can provide that first level monitoring quite well and there are a number of ZenPacks available to provide SQL Server monitoring. However I do find it quite challenging to come up with a in-depth SQL monitoring from it, but this is purely because I am use to a Windows Scripting environment.

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
I previously blogged about this (and some other open-source tools). This is still my fav free open-source offering, and a great option if you do not have but budget for SCOM and if you are willing to write your own client front-end, or if you need to plug into an existing monitoring front-end.

I hope this information helps you to find a way to monitor your SQL database environment. Feel free to post comments should you need more info.


Thanks again to everyone that attended the session.

Infrastructure Saturday

Today is Infrastructure Saturday at Microsoft Brisbane, I'll be there presenting from 12:35pm – 1:20pm in Theater 2......

More info at http://infrastructuresaturday.org/

TSQL to report DB Mirroring status and configuration

Have you been faced with the need to report on, or verify the configuration of your SQL Server Database Mirroring. Well maybe you have or maybe you haven't, if you only have a small number of databases with DB Mirroring then this may not be very useful, but for me with 50-100 databases with DB Mirroring I needed a way to quickly check what the status and safety level of the mirrored databases were. Not to mention the fact that I needed to easily identify the databases that were no mirrored to report back to the application owners so they knew what was not in a DR configuration.

Hopeful this query can save you some time or at least give you a starting point if facing a similar situation:

SELECT a.name
, ISNULL(b.mirroring_role_desc, 'NOT MIRRORED') as mirroring_role_desc
, ISNULL(b.mirroring_state_desc, '') as mirroring_state_desc
, ISNULL(mirroring_safety_level_desc, '') as mirroring_safety_level_desc
FROM sys.databases a
INNER JOIN sys.database_mirroring b on b.database_id = a.database_id
WHERE a.name NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution')
ORDER BY a.name

Export the SSRS Report Server's folder / item structure using TSQL

If your faced with the task of migrating your Reports from SSRS 2000/2005 to SSRS 2008 as I am then you're probably looking for a way to export your folder structure to help with the planning. You might be even using the migration as a way to clean up the security and how some of the items are stored (e.g. Data Sources). All of this is not easy without a way to export the folder/item structure from your existing environment. The following TSQL should help you gain an overview of your structure and allow you to start planning the migration.

Use ReportServer;
SELECT dbo.Catalog.Path
, dbo.Catalog.Name
, dbo.Catalog.Description
, dbo.Catalog.Type
, (CASE
WHEN dbo.Catalog.Type = 1 THEN 'Folder'
WHEN dbo.Catalog.Type = 2 THEN 'Report'
WHEN dbo.Catalog.Type = 3 THEN 'Image'
WHEN dbo.Catalog.Type = 4 THEN 'Linked Report'
WHEN dbo.Catalog.Type = 5 THEN 'Data Source'
WHEN dbo.Catalog.Type = 6 THEN 'Model'
ELSE 'Type_'+CONVERT(char(2), dbo.Catalog.Type)
END) as Type_Name
, dbo.Catalog.CreationDate
, dbo.Catalog.ModifiedDate
, ISNULL(dbo.Catalog.Hidden, 0) as Hidden
, Parent.Name as Parent_Name
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
ORDER BY dbo.Catalog.Path, Parent.Name

In addition to this the following TSQL will provide you with a list of the assigned users and roles to each folder to help with preparing permissions during migration:

SELECT dbo.Catalog.Path
, dbo.Users.UserName
, dbo.Roles.RoleName
FROM dbo.Catalog
INNER JOIN dbo.Catalog Parent ON Parent.ItemID = dbo.Catalog.ParentID
INNER JOIN dbo.PolicyUserRole ON dbo.PolicyUserRole.PolicyID = dbo.Catalog.PolicyID
INNER JOIN dbo.Users ON dbo.Users.UserID = dbo.PolicyUserRole.UserID
INNER JOIN dbo.Roles ON dbo.Roles.RoleID = dbo.PolicyUserRole.RoleID
WHERE dbo.Catalog.Type IN (
1 -- Folder
)
ORDER BY dbo.Catalog.Path, dbo.Users.UserName

When it comes time for the actual migration you might want to look at using a tool such as RS Scripter..... I know I will be using that tool for most of my migration

Useful Open Source SQL Tools

The following are some useful Open Source tools available to assist with managing and administering a MS SQL Server:

SqlMonitoring Tool
http://sqlmonitoring.codeplex.com/
Also mentioned in http://www.codecapers.com/post/free-scom-alternative-for-sql.aspx

Open DBDiff
http://opendbiff.codeplex.com/

Performance Analysis of Logs (PAL) Tool
http://pal.codeplex.com/

Hopefully others will find them as useful as I have with simplifying my day-to-day DBA tasks.