Separate Login for a query in a stored procedure

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

  • 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.

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

  • 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
  • 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

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

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

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

  • bhuvnesh.dogra (8/13/2008)


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

    Very, very bad idea. The mapping is fine, but it should be mapped to an account with minimal privileges.

    What you've just done is essentially given everyone who can log in to the first server full sysadmin rights on Srvr1 as a user needs very few permissions to access a linked server.

    I strongly suggest you create a new login on Srvr1 with just the permissions needed and use that for the linked server mapped login

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

    Books online should have a section on this.

    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
  • Dude please tel how to do it

    i have domain\sqlman account with admin rights on srvr1

    but if i use it as remote login in linked server then it says

    login failed for 'domain\sqlman':unsure:

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • bhuvnesh.dogra (8/14/2008)


    Dude please tel how to do it

    I did.

    Create a SQL login on that server.

    Give that login just the rights that it needs for what you are doing across the linked server

    Go back to the first server and change the linked server security settings to use the new login, not 'sa'

    i have domain\sqlman account with admin rights on srvr1

    but if i use it as remote login in linked server then it says

    login failed for 'domain\sqlman':unsure:

    Is either that login or builtin\administrators in the login list of that SQL Server?

    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
  • If i add domain\sqlman as remote login it gies below error:

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

    and yes bulitin\adminstartor is present in srvr1 and srv2 but how should i use it ?

    Please help

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • bhuvnesh.dogra (8/14/2008)


    If i add domain\sqlman as remote login it gies below error:

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

    That's not what I asked.

    and yes bulitin\adminstartor is present in srvr1 and srv2 but how should i use it ?

    You don't use it. I just wanted to check.

    I don't know why the domain logins are failing. Stick with the mapped logins that you got working, just make sure that the login that you are mapping to on the linked server security tab is not sysadmin.

    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

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply