Script to Retrieve Security Information – SQL Server 2012 (Denali) and above

Written by VidhyaSagar. Posted in Scripts

SQL Server 2012 codenamed SQL Server Denali is on the way to market by 2012. As you all know SQL Server 2012 has new features related to security, we have contained database where you are allowed to create contained users and the next feature is server level roles, other security features are not related to this article, we will cover those later. I wrote some scripts earlier to retrieve permissions from SQL 2000 and SQL 2005 (links below) however these scripts are no longer useful for SQL Server 2012 as it won’t return the new contained user and server level role permissions.

VN:F [1.9.13_1145]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

Startup procedure to check SQL Server status when restarted

Written by VidhyaSagar. Posted in Scripts

I’ve written a startup procedure to check SQL Server status when the SQL Server gets restarted. Most of the time as a DBA when a Server gets rebooted or SQL Services is restarted we need to make sure everything is fine in SQL server to make sure SQL server is running healthy. Some companies will have budget to buy a monitoring tool, so that all these things will be taken care by that tool. This small procedure will help the DBA’s to know the status of SQL Server in HTML format through mail when it’s restarted.

VN:F [1.9.13_1145]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

Database Documentation

Written by Deepak. Posted in Scripts

Almost every application DBA would have a need to document the various database objects such as tables, views, stored procedures and functions. I wasn’t an exception and exploring ways to document the 400+ tables we have. Basically, this would serve the following purpose:

VN:F [1.9.13_1145]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.13_1145]
Rating: -1 (from 1 vote)

Recent Objects – Without Triggers

Written by Deepak. Posted in Scripts

I was thinking about writing a script to capture the objects that were either CREATED/DELETED or MODIFIED in the last 24hrs. Instead of directly querying the sys.objects I from where I can only get minimal information I decided to get a whole bunch of useful information using the SQL Server default trace.

By default the default trace will be running in all the SQL Servers since SQL 2005 onwards. It will capture minimal information without overloading the database. It will be placed in the same location as that of the SQL Server errorlog. The maximum file size is 20MB after which the trace will roll over into a new file. However, SQL Server can maintain only the 5 most recently created trace files. In addition a new trace file is generated whenever SQL Server is restarted.

VN:F [1.9.13_1145]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.13_1145]
Rating: 0 (from 0 votes)

Recent Comments

VidhyaSagar

|

Thanks Ashish, I’ve updated the script.

Ashish

|

I was just searching some trace related articles and gone through your article. Found one incorrect information,
to disable the trace, the command should be
dbcc traceoff (….)
you might have by mistake mentioned it as traceon for disabling as well.

Ben

|

I remember having to write a complicated procedure to do the same thing recently. This will be nice to use with a web app to only return a small subset. I think this will improve the speed especially when the entire result set is very large.

namanthakral

|

got errors after executing this trigger
“SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.”

and was really stucked in this mess but DAC saved my life
C:\Users\Administrator>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER block_ipaddress ON ALL SERVER
2> go