Home > Cannot Be > Grant Impersonate On User

Grant Impersonate On User

Contents

When he returns from vacation, he can simply execute a REVOKE to remove the impersonate permission. We've got lots of great SQL Server experts to answer whatever question you can come up with. This has be to run in the context of the database which has problem, as shown below. Reply to Topic Printer Friendly Author Topic MuadDBA Aged Yak Warrior USA 628 Posts Posted-07/31/2013: 16:27:14 I have the following sproc which is trying to gather information from

Success CenterAssetsSearchSuccess CenterPatch ManagerAlert CentralCustomer ServiceDameWare Remote Support & Mini Remote ControlDatabase Performance Analyzer (DPA)Engineer's ToolSet (ETS)Enterprise Operations Console (EOC)Failover Engine (FoE)Firewall Security Manager (FSM)Free Tools Knowledge BaseipMonitorIP Address Manager (IPAM)Kiwi Grant execute permissions on the procedure created in #4 to retrieve_plan userThe above works in my caseAmit Banerjeewww.TroubleshootingSQL.comwww.facebook.com/TroubleshootingSQLhttp://twitter.com/banerjeeamit MuadDBA Aged Yak Warrior USA 628 Posts Posted-08/01/2013: 13:26:47 Thanks Amit. But from other accounts it throws exception: Msg 15406, Level 16, State 1, Procedure sp_NovusGetWithdrawalDetails, Line 19 Cannot execute as the server principal because the principal "ExactMetastorm" does not exist, this I have fixed the orphan users. http://stackoverflow.com/questions/798206/problem-with-sql-server-execute-as

Grant Impersonate On User

current community chat Stack Overflow Meta Stack Overflow your communities Sign up or log in to customize your list. Be aware of the elevated rights needed by the account executing SETUSER and the security risk involved. To return to the user, go to http://instance.service-now.com/logout.do and log back in. http://support.microsoft.com/kb/314546/en-us Thanks, Sofiya Li Sofiya Li TechNet Community Support

Marked as answer by Sofiya LiMicrosoft contingent staff, Moderator Friday, December 06, 2013 6:26 AM Monday, December 02, 2013 6:51 AM

troubleshootingsql Starting Member 3 Posts Posted-08/01/2013: 09:15:30 Have you tried something like this:alter PROCEDURE [dbo].[Sproc_Execution_Plan_Get]( @Plan_Handle VARCHAR(75))ASSET NOCOUNT ONexecute as login = ''; That is, this works just fine: create user TestUser without login go exec ('select 3') as user='TestUser' I do not want to recreate all those triggers, so is there any way If the above does not resolve your issue please delete and recreate the user in SQL Server Management Studio (SSMS)for the server logins and the Eminentware logins. Sql Impersonate Retrieved from "http://wiki.servicenow.com/index.php?title=Impersonating_a_User&oldid=234110" Categories: Auditing and System LogsUsers and GroupsContents > Administer > Security > Auditing and System Logs Contents > Administer > User Administration > Users and Groups Was this

SELECT @Plan_Handle = CP.plan_handle FROM sys.dm_exec_cached_plans AS CP CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st WHERE OBJECT_NAME(st.objectid,st.dbid) = @Stored_Procedure_Specific_Name One thing to note: I have tried using "SETUSER" on this DB (which is This Type Of Principal Cannot Be Impersonated Or You Don't Have Permission Terms of Use Post navigation ← How To Find SQL Service Account Using SQLScript Hello Processing → Category.NET Android Learning Leisure Milestone Processing Python R Scala SQL SSIS Webiste Meta Register Log in Entries Going with common sense, I went ahead to check the owner of the database, which is generally visible in database > right click > properties.

Related changes Special pages Permanent link This page was last modified 15:16, 10 July 2014. The Proposed New Database Owner Is Already A User Or Aliased In The Database. Please reset the password for credentialUnable to deploy revised publishing package with SolarWinds customizationUnable to deploy WMI Providers - Input string was not in a correct formatUnable to edit third party Not sure what right/Permissions I am missing? Multiple calls can be made and the context will continue to switch until reverted back to the original context by calling SETUSER without a username.

This Type Of Principal Cannot Be Impersonated Or You Don't Have Permission

GRANT IMPERSONATE ON USER:: YourUser TO HistoryUser More details here http://msdn.microsoft.com/en-us/library/ms181362.aspx share|improve this answer answered Apr 28 '09 at 14:44 Eoin Campbell 29k1373127 Nop, doesn't help. –Vilx- Apr 28 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=187256 For further details on cookies, please see our cookies policy. Grant Impersonate On User As with SETUSER, it is possible to constrain the impersonation to prevent it from returning the context to the original account. Cannot Find The Principal 'dbo', Because It Does Not Exist Or You Do Not Have Permission. EXECUTE AS LOGIN = 'jdoe' WITH NO REVERT EXEC sp_SaveOrderItem 'B123', 1, '9484844' REVERT Permissions Whereas SETUSER required sysadmin permissions, EXECUTE AS USER requires just dbo permissions to execute the statement.

sp_helpdb sp_helpdb ‘DatabaseName’ T-SQL which is used by sp_helpdb select name, isnull(suser_sname(sid),'~~UNKNOWN~~'), convert(nvarchar(11), crdate), dbid, cmptlevel from master.dbo.sysdatabases As we can see above, “~~UNKNOWN~~” is not something which is not Books Online mistakenly states users with dbo rights can also utilize this statement, however this does not work. Verify that the connection information for the Patch Manager is correct and the Data Grid Service is started.SQL account permissions for Patch ManagerSQL account permissions required by Patch ManagerSQL database size Now, I can execute the procedure from certain accounts. Microsoft Sql Server Error 15517

Information for: Enterprise Small Business Consumer (Norton) Partners Our Offerings: Products Products A-Z Services Solutions Connect with us: Support Connect Communities Security Center Find a Partner Events Webcasts Contact Us About The REVERT at the end has no effect on the context. Create a SymAccount now!' Error 'Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission' Select the user to impersonate Click OK. 4 Impersonating a User on a Mobile Phone The impersonation icon is not visible in the mobile view of the platform, and impersonating is

I have fixed the orphan users. Sp_change_users_login Since we are not able to use UI, we have to use sp_changedbowner (change db owner with no spaces). Provide feedback on this article Request Assistance Print Article Products Subscribe to this Article Manage your Subscriptions Search Again Description When trying to import SQL Server assets from a CCS

Not sure what right/Permissions I am missing?

How did early mathematicians make it without Set theory? Anyone know what it is? DETECT: USE ; GO; sp_change_users_login @Action='Report'; GO; RESOLVE: The following command relinks the server login account specified by with the database user specified by : USE ; GO sp_change_users_login @Action='update_one', The Server Principal Is Not Able To Access The Database Under The Current Security Context sql-server impersonation share|improve this question edited Apr 10 '10 at 21:36 John Saunders 138k20179324 asked Apr 28 '09 at 14:18 Vilx- 50k57194332 add a comment| 4 Answers 4 active oldest votes

The system account logs into the SQL Server database and issues the following: EXECUTE AS USER = 'jdoe' EXEC spSaveOrderItem 'B123', 1, '9484844' REVERT Issuing the REVERT statement at the end Notice how the context reverts to the logged in account with the first execution of SETUSER without a username. Logging can be enabled/disabled with the glide.sys.log_impersonation property. 8 Forcing Logout In some cases, impersonating a user might cause an issue that makes it difficult to switch back (e.g. For More Information The following links can provide additional information for the concepts covered here: EXECUTE AS - http://msdn.microsoft.com/en-us/library/ms181362.aspx REVERT - http://msdn.microsoft.com/en-us/library/ms178632.aspx SETUSER - http://msdn.microsoft.com/en-us/library/ms186297.aspx TRUSTWORTHY - http://msdn.microsoft.com/en-us/library/ms187861.aspx » See All

This means something is not right with owner of the database? Don't have a SymAccount? Why does Friedberg say that the role of the determinant is less central than in former times? Open SSMS and navigate to the Eminentware db > Security > Logins and select the "ewreportuser" Right-click > Delete Open up the Server Security > Logins and select the "ewreportuser" Right-click

To do this with EXECUTE AS, add WITH NO REVERT to the end of the statement as below. You'll need to grant that user IMPERSONATE priviledges for the User Account HistoryUser.