SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Separate Login for a query in a stored procedure


Separate Login for a query in a stored procedure

Author
Message
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6826 Visits: 4076
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;-)
andrew gothard
andrew gothard
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1430 Visits: 5863
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.
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6826 Visits: 4076
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;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113854 Visits: 45440
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, MVP, M.Sc (Comp Sci)
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


GSquared
GSquared
SSC-Dedicated
SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)SSC-Dedicated (30K reputation)

Group: General Forum Members
Points: 30047 Visits: 9730
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
Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6826 Visits: 4076
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;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113854 Visits: 45440
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, MVP, M.Sc (Comp Sci)
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


Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6826 Visits: 4076
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;-)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)SSC Guru (113K reputation)

Group: General Forum Members
Points: 113854 Visits: 45440
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, MVP, M.Sc (Comp Sci)
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


Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6826 Visits: 4076
thanks...........SmileSmileSmileSmile .vry mich .myjob started finally after vigorous efforts of 3 working days...thnx once againSmile.

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;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search