August 11, 2008 at 5:42 am
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;-)
August 11, 2008 at 5:51 am
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.
August 11, 2008 at 6:21 am
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;-)
August 11, 2008 at 6:38 am
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
August 11, 2008 at 10:45 am
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
August 13, 2008 at 5:59 am
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;-)
August 13, 2008 at 6:37 am
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
August 13, 2008 at 6:48 am
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;-)
August 13, 2008 at 7:07 am
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
August 13, 2008 at 11:28 pm
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;-)
August 14, 2008 at 1:12 am
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
August 14, 2008 at 1:21 am
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;-)
August 14, 2008 at 1:28 am
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
August 14, 2008 at 1:46 am
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;-)
August 14, 2008 at 2:12 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply