Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Separate Login for a query in a stored procedure Expand / Collapse
Author
Message
Posted Monday, August 11, 2008 5:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
hi,

Is it possible to execute any particular query in any stored Proc with different login name.?

suppose i have logged in sql server 2005 with "sa" but in one of my stored proc i want to execute a query with a other domain account "DOMAIN\bkumar'

Is it possible ?

Please suggest


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #550147
Posted Monday, August 11, 2008 5:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 311, Visits: 3,511
Hi,
yes - you can use EXECUTE AS - see http://msdn.microsoft.com/en-us/library/ms188354.aspx for an explanation


I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Post #550155
Posted Monday, August 11, 2008 6:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
hi
I will elaborate more about the problem.
first server has sql agent login : DOMAIN\sqlman (sysadmin)
AND second server has its sql agent login : DOMAIN\sqlman (sysadmin)
More over both server also have same sql login : DOMAIN\sqlman

Actually on running Stored proc manually it works fine but when i run it in JOB
it gives error :

Executed as user: DOMAIN\SQLMan. Login failed for user 'DOMAIN\bkumar'. [SQLSTATE 28000] (Error 18456).


Here,

DOMAIN\bkumar is server windows login which has adminstrative rights on both servers


please suggest


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #550177
Posted Monday, August 11, 2008 6:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,385, Visits: 36,828
Looks like DOMAIN\bkumar doesn't have login rights on SQL Server. Just because its a local administrator doesn't mean that it has login rights to SQL.

Go into the SQL logins and cehck to see if that login is listed, or if Builtin\administrators is there.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #550193
Posted Monday, August 11, 2008 10:45 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
If you set up roles on the server (Security tab), you can tell a job to run under a particular login/role.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #550438
Posted Wednesday, August 13, 2008 5:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
thanks ......but it didnt work

the job contains following code:
---------------------------------------------------
EXECUTE AS LOGIN = 'domain\sqlman'
GO
DECLARE @EndDate DATETIME

SET @EndDate = GetDate()

EXEC dbo.ap_mtn_Sync_Table_data @EndDate, 'LU_'
------------------------
with above code it gives below error :

Executed as user: 'domain\SQLMan. SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 42000] (Error 65535) OLE DB provider "SQLNCLI" for linked server "idb\DEV" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI" for linked server "Idb\DEV" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". [SQLSTATE 01000] (Error 7412). The step failed.


---------
but if i exclude [execute as login = 'domain\sqlman' ] from above code

it gives :

Executed as user: domain\SQLMan. Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [SQLSTATE 28000] (Error 18456). The step failed.

Please suggest


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #551788
Posted Wednesday, August 13, 2008 6:37 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,385, Visits: 36,828
What does the procedure "ap_mtn_Sync_Table_data" do, and what are the security settings of the linked server that it is using?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #551821
Posted Wednesday, August 13, 2008 6:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
procedure :
------------------------

CREATE Proc [dbo].[ap_mtn_Sync_Table_data]
@EndDate DATETIME,
@prefix VARCHAR(100)
AS

SET NOCOUNT OFF
SET XACT_ABORT ON

CREATE TABLE #tmp_table(table_name VARCHAR(50),id INT IDENTITY)
DECLARE @SQLcmd NVARCHAR(400),@TableName NVARCHAR(50), @StartDate DATETIME
DECLARE @i INT ,@max INT

SELECT @i = 1, @StartDate = last_used_date FROM vbr_db.dbo.HQ_to_IN_LU_CONTROLLER

SET @SQLCmd = 'INSERT INTO #tmp_table (table_name) SELECT OBJECT_NAME(OBJECT_ID) FROM sys.dm_db_index_usage_stats ' +
' WHERE database_id = (SELECT database_id FROM sys.databases WHERE NAME = "vbr_db")' +
' AND index_id in (0,1) ' +
' AND last_user_update BETWEEN ''' + CONVERT(VARCHAR(25),@StartDate,121) + ''' AND ''' + CONVERT(VARCHAR(25),@EndDate,121) + ''' AND OBJECT_NAME(OBJECT_ID) LIKE ''%' + @prefix + '%'''

EXEC(@SQLCmd)
CREATE INDEX IX_#tmp_table_table_name ON #tmp_table(table_name)
SELECT @TableName = table_name, @i = @i + 1 FROM #tmp_table WHERE @i = ID

WHILE @@rowcount <> 0
BEGIN

SET @SQLCmd = 'DELETE FROM vbr_db.dbo.' + @TableName
EXEC(@SQLCmd) AT srvr2

SET @SQLCmd = 'INSERT INTO [vbr_db].dbo.' + @TableName + ' SELECT * from srvr1.vbr_db.dbo.' + @TableName + ' WITH (NOLOCK)'
EXEC(@SQLCmd) AT srvr2

SELECT @TableName = table_name, @i = @i + 1 FROM #tmp_table WHERE @i = ID
END




here , two query are to be get executed at linked server (srvr2)

domain\sqlman is the service account user who has admin rights...on both servers



the strange thing is that if i run manually it works fine


please reply






-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #551831
Posted Wednesday, August 13, 2008 7:07 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:44 AM
Points: 40,385, Visits: 36,828
What's the security settings on the linked server "srvr1"?
(In object explorer, Server object -> Linked servers, right click that linked server, choose properties, go to the security tab)



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #551853
Posted Wednesday, August 13, 2008 11:28 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:03 AM
Points: 2,840, Visits: 3,975
thanks...........:):):):) .vry mich .myjob started finally after vigorous efforts of 3 working days...thnx once again:).

Earlier "Be made using logins current security context" was enabled

But i enabled 4th option where i nedded to put remote login nad i put sa authentication.



Can you tell me the functionality od all 4 option..


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #552415
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse