.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

EXECUTE AS inside a login trigger (or a reasonable alternative)

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Here's the scenario: we use a 3rd party application that has a really awful idea of security. It uses Windows authentication, but requires direct database (and table) access, and generally doesn't work properly without full db_owner database access. We also run a number of other applications that use Windows Authentication, and the collateral damage is that they have way more privileges than needed to run.

In an effort to improve this, if even only slightly, I'm trying to limit these excessive permissions to just this one application. Now, I know what you're thinking already - "Application roles!" I've looked into this, but as best I can tell, an application role is a database principal, and not a server principal. This presents a problem, as this application needs to access multiple databases at once.

I've found that issuing EXECUTE AS LOGIN = 'dedicated_application_login' behaves exactly the way I would like, but only when issued in an ad-hoc batch. If I place it in a trigger or procedure, it seems to only affect execution of the module, and doesn't permanently change the security context of the session. This prevents me from checking the application name in a login trigger and running EXECUTE AS if everything checks out - I can do it, but it won't really have any meaningful effect. I'm well aware this is awful security as well, but i

View Complete Post

More Related Resource Links

execute update timedout expires , table with trigger due to recovery of database


I have table A in database A  , in table A i have trigger to insert record to database B table A, each update and insert of database A -table A, triiger fire and insert record in database B table A, when i try to update some times asp.net application gives error

"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."

and then error log says System.Data.SqlClient.SqlException: Database 'database B is being recovered. Waiting until recovery is finished.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean ret

Execute inside Execute

Is it possible to use EXECUTE inside an EXECUTE command?  For example, I build a dynamic SQL statement like so:
 SET NOCOUNT ON DECLARE @SQL NVARCHAR(MAX) = ''; SELECT @SQL = @SQL + 'EXEC MSDB.dbo.sp_update_job @job_name = ''' + j.[Name] + ''', @owner_login_name = ''sa''; ' + CHAR(13) FROM MSDB.dbo.sysjobs j   LEFT JOIN Master.dbo.syslogins l     ON j.owner_sid = l.sid WHERE   l.[name] <> 'sa'   OR l.name IS NULL ORDER BY j.[name] PRINT @SQL;
I can execute the statements that come from that manually and everything works... However, if I change that PRINT @SQL to an EXECUTE @SQL, it gives me errors: Msg 203, Level 16, State 2, Line 15 The name 'EXEC MSDB.dbo.sp_update_job @job_name = 'job1', @owner_login_name = 'sa'; EXEC MSDB.dbo.sp_update_job @job_name = job2', @owner_login_name = 'sa'; EXEC MSDB.dbo.sp_update_job @job_name = 'job3', @owner_login_name = 'sa'; ' is not a valid identifier. Is this because you can't do an EXECUTE inside an EXECUTE?  Is there any way to do what I'm trying to do which is enumerate all SQL jobs that don't have an owner of 'sa' and update them so they do? Number2 - (John Nelson) Microsoft MVP (2009) - System Center Configuration Manager http://number2blog.com

Need to execute ALTER DATABASE inside stored procedure


SQL 2005 Standard.

I wrote a sp that uses ALTER DATABASE instruction. When I execute sp with elevated user it works fine; when I try to execute that sp with a normal user (I alerady gave execute permission for that user) it fails with "alter database failed".

I tried to modify create proc with execute as owner but nothing changed.

How can I do ?


Passing Parameters to Execute SQL task which is inside a For Loop Container



I have a requirement.

In this I will get a value of 50 ids from one table.

I have an sp which will take ids as input parameter and insert values into another table.

My Problem is I am having a for loop container with me and inside that i have execute sql task in which I have stored procedure.

How can i map the id parameter to the execute sql task variable whichi is inside a forloop.




Thanks, A2H

Using MVVM and commands inside of a datatemplate, the command wont execute.

Inside of my main view i have a datatemplate showing off all the items in an observable collection. now inside of that i want to set commanding on a radio button.

outside the datatemplate this works
<Button Height="23" Name="btnUpdate" Command="{Binding UpdateCommand}"

How to display a message in SQL Server 2008 inside a trigger?

I'm using a simple try-catch block inside a trigger. On catch, I rollback the trans but I also want to be able to display some sort of message to myself (the error) since I'm having a hard time debugging the errors in my triggers. It is a after delete, update trigger and whenever I delete a record, the message displayed is transaction ended in a trigger and since there are multiple try-catch blocks, i don't know which one threw the catch and why it did.

Using sp_grantlogin inside a stored procedure that uses EXECUTE AS.


Hello to all you oh mighty of the mightiest programming gods!

I am a novice in need of help.

I have a stored procedure



PROCEDURE [dbo].[vm_upd_user_rights]




Accessing the different controls inside a GridView control

how we can access a particular control which resides inside a GridView control. In this article I will show you how you can access different controls inside a GridView control. We will see how we can access a TextBox control, a DropDownList control and a ListBox control. If you are working with ASP.NET 1.X then you might want to check out my article Accessing Different Controls Inside a DataGrid.

How To Add calendar control inside a GridView

This article show hot to add calendar control inside a GridView with code in c#

Using ATLAS PopUp Control with Calendar inside the GridView Control

In this video tutorial Mohammad demonstrates how you can access the Calendar control which is displayed using the ATLAS PopUp control inside the GridView control.

RadioButton inside GridView, How to get it work as normal

Did you tried before to drag a RadioButton control inside a Gridivew templatefield, and then you attempt to select these RadioButtons , you will notice that the behavior of RadioButton control will be changed and it will work just like the behavior of checkbox control! the user will be able to select more than one radiobutton in the grid!

Maintain HTML Radio Button Selection Inside GridView After Postback

Long time ago I wrote an article about getting the value of the selected RadioButtons inside the GridView control. The code did not addresssed the postback issue which means that the selected radio button was cleared after the postback occurs.

HttpContext.Items and Server.Transfer/Execute

The problem was basically that when a Server.Transfer call was applied the script container (and also various ClientScriptProxy script embedding routines) would potentially fail to load up the specified scripts.

I thought this would be simple... Trying to create a login does an ajax check before submit


I have the standard .net login control that is working.  (Still learning .net after 15yrs of classic)

What I want to do is OnButtonClick run a javascript function (to do an ajax call to the db to check if login is valid or not) and if it returns true, then submit the form normally, else don't submit. The piece that I can't figure out, is how to correctly override the default click even to hit my JS routine and then submit the form properly afterwards.

Thanks ahead of time

want to execute url without showing that page or url to client.


hello friends,

I need ur help,what i want to do is to execute a url ("To send a text message to client") on client registration but without showing that url page, in short executing url without displaying any page or url to the client.

please help and thanks in advance.

LinkButton inside Repeater



I have Repeater, which consists of LinkButon and Literal. LinkButon and Literal are whole thing by implication. I want get value from Literal, when user click on corresponding LinkButton.


Textbox in ItemTemplate inside Gridview, unable to get the value


Hi. I have a itemtemplate inside a gridview.

<asp:TemplateField HeaderText="Enter OTP">
                            <asp:Label ID="Label2" runat="server"
                                style="font-family: Arial, Helvetica, sans-serif; font-size: x-small"
                                Text="PIN Num:"></asp:Label>
                            &nbsp;<asp:TextBox ID="txtPIN" runat="server" Height="21px" Width="100px"></asp:TextBox>

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend