DDL Trigger to Automate Permission

June 8, 2012 Leave a comment

This bit is related to object permission in DB and how did we automate it.

In our DB env we use role based security for every db .Whenever any SP or function is deployed in DB we grant appropriate permission
to role in that DB. Direct access to tables\views is not granted to application logins. We needed to come with solution to grant appropriate
permission when ever new sp\function is created in DB. So DDL triggers come to rescue.

Here is small script I deployed in my DB so that we don’t miss permission grant to new objects. Works great for us. No more worries about permission.

CREATE TRIGGER Trg_Module_Perm_DDL
ON DATABASE
FOR CREATE_FUNCTION, CREATE_PROCEDURE
AS BEGIN
SET NOCOUNT ON;
DECLARE @ObjectName AS NVARCHAR (100),
@fntype AS VARCHAR (5),
@EventType AS NVARCHAR (50),
@Grant AS NVARCHAR (300);

SET @EventType = EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(50)’);

SELECT @fntype = type FROM sys.objects
WHERE name = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

SELECT @ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

IF @EventType = ‘CREATE_PROCEDURE’
BEGIN
SELECT @Grant = ‘Grant Execute on ‘ + @ObjectName + ‘ TO DBRole’;
END
IF @EventType = ‘CREATE_FUNCTION’
BEGIN
SELECT @Grant = CASE @fntype
WHEN ‘FN’ THEN ‘Grant Execute on ‘ + @ObjectName + ‘ TO DBRole’
WHEN ‘IF’ THEN ‘Grant Select on ‘ + @ObjectName + ‘ TO DBRole’ ELSE ‘None’
END;
END
BEGIN TRY
–Print @Grant
IF @Grant <> ‘NONE’
BEGIN
EXECUTE (@Grant);
END
END TRY
BEGIN CATCH
RAISERROR (‘Error while Granting permission to the proc %s’, 16, 1, @ObjectName);
ROLLBACK;
END CATCH
END

If you have multiple DBs then this needs to be deployed to every DB you need automated permission grant. You can generate separate script for every
DB or even can keep one script for every DB. For one script you might need to modify above script by adding variable for rolename and conditionally
setting value of this variable based on DB_Name() function output. Here is updated script.

CREATE TRIGGER Trg_Module_Perm_DDL
ON DATABASE
FOR CREATE_FUNCTION, CREATE_PROCEDURE
AS BEGIN
SET NOCOUNT ON;
DECLARE @ObjectName AS NVARCHAR (100),
@fntype AS VARCHAR (5),
@EventType AS NVARCHAR (50),
@Grant AS NVARCHAR (300),
@RoleName AS NVARCHAR (20);

SET @EventType = EVENTDATA().value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(50)’);

SELECT @fntype = type FROM sys.objects
WHERE name = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

SELECT @ObjectName = EVENTDATA().value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(100)’);

IF DB_Name() = ‘DB1’
SET @RoleName = ‘DB1Role’;
ELSE
IF DB_Name() = ‘DB2’
SET @RoleName = ‘DB2Role’;
ELSE
SET @RoleName = ‘DBRole’;
IF @EventType = ‘CREATE_PROCEDURE’
BEGIN
SELECT @Grant = ‘Grant Execute on ‘ + @ObjectName + ‘ TO ‘ + @RoleName + ‘;’;
END
IF @EventType = ‘CREATE_FUNCTION’
BEGIN
SELECT @Grant = CASE @fntype
WHEN ‘FN’ THEN ‘Grant Execute on ‘ + @ObjectName + ‘ TO ‘ + @RoleName + ‘;’
WHEN ‘IF’ THEN ‘Grant Select on ‘ + @ObjectName + ‘ TO ‘ + @RoleName + ‘;’ ELSE ‘None’
END;
END
BEGIN TRY
PRINT @Grant;
IF @Grant <> ‘NONE’
BEGIN
EXECUTE (@Grant);
END
END TRY
BEGIN CATCH
RAISERROR (‘Error while Granting permission to the proc %s’, 16, 1, @ObjectName);
ROLLBACK;
END CATCH
END

Now we can add all kind of fancy stuff here. For eg. you can send all this information including grant statement to logging table or grant permission
to other login\role if needed. Couple of small tweaks to above script will do that for you..

So here is my simple method to automate permission grant for some common DB routines. There are several methods to do this so use whatever works best
for you.

Any comments , suggestions , improvements welcome.

–Neeraj

Advertisements
Categories: Sql Server

Script Indexes on all Tables using PowerShell.

September 19, 2011 1 comment

This is going to be quick post.. This morning I got requested to script only indexes on all tables in DB.

First I tried to search around in SSMS to see if there is any option to script only indexes on all tables but no luck. Finally after an hour of work with my little knowledge of PowerShell I was able to generate all the index script.. This is going to be part of my scriptbox for future usage.

In SSMS browse to tables and right click. Select start powershell . This will open powershell console window with path set to tables. Run below script to script indexes only.

Script :

$tbl = gci | select-object name
foreach($t in $tbl)
{
$path = “”
$path = “dbo.”+$t.name+”\indexes\”
gci -Path $path | %{$_.Script() | out-file c:\index.txt -append; “GO `r`n ” | out-file c:\index.txt -append; $_.Name;}
}

There might be several other better ways in powershell to do it but for now I could get this work using above script.. I would try to modify this script by adding options to Drop\Create indexes rather than create only in future version if get chance 🙂

NJ

Categories: Uncategorized

Recover Lost .RDL Files *

May 17, 2011 1 comment

 

Recently , per company wide policy my office machine was upgraded to Windows Server 2008 . Even after backing up everything I missed lot of my precious technical arsenal. That included my diagnostic scripts and number of .rdl files that I created and published for my DB admin work. For a moment I was distraught. Feeling of creating more than 50 reports again was painful but right at that moment I realized that I had not deleted my old ReportServer DB . Kind of blessing out of nowhere.

But again I wasn’t sure if ReportServer DB keep definition of every RDL and datasources. After 30 mins of table surfing I found out it does. Voila

I attached my old ReportServer DB and queried table dbo.Catalog  . Catalog table keeps definition of all the data sources and reports. Catalog table have column named “content” which is of image datatype .

You can run below query to get the definition of data sources and .rdl file from Catalog table. Value of column Type distinguish between the datasources and reports.

For datasources Type would be 5 and for reports Type would be 2 so for getting the definition of reports in Xml format we can run below query.

   1: SELECT

   2:      NAME,CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML) AS ReportDef

   3:   FROM dbo.catalog

   4:   WHERE TYPE = 2

 

For datasources :

   1: SELECT

   2:       NAME,CAST(CAST(CONTENT AS VARBINARY(MAX)) AS XML) AS ReportDef

   3:    FROM dbo.catalog

   4:    WHERE TYPE = 5

This would output definition in Xml format.

Rename the xml output as .rdl for reports and .rds for data sources. All set. Reports and datasources are ready again.

I had to make couple of other changes in .rds file which might be specific to my env. I was seeing some xml format error. So I created one dummy datasource and grabbed couple of xml format specific lines from dummy datasource .

That saved me hours of time that I would have spent creating reports and re-writing all the scripts.

Good luck until next tidbit.

–NJ

Categories: Uncategorized

My First Writing–Intro

May 11, 2011 Leave a comment

 

My Name is Neeraj Jandwani. I live in San Francisco Bay Area . Working as Senior Sql Server DBA .. Time just flies as I didn’t realize that it’s been 10 years I have been working with Sql Server. Yes, I know I am late comer in blogging world but It’s never too late. Here I will be blogging about my experiences with Sql Server and related technologies..I hope I don’t procrastinate and start blogging for real this time.

Ok..Will be back soon.

Neeraj

Categories: Uncategorized