Create Stored Procedure or SQL Job GRANT ALTER ANY LOGIN

[katekis]

I am trying to create a stored procedure or SQL Job to run the following command.

use Master

GRANT ALTER ANY LOGIN TO "domain\login"

GO

I can run the above command just find manually from a SQL Query but when I try to creat a SQL Job and run the command I get a syntax error? Does anyone know why I can not create a SQL Job or a Stored procedure to run the above command? I am using SQL Server 2008 R2.

 

[djj55]

You cannot use a USE in a stored procedure. Why do you want a stored procedure to alter a login?

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[TysonLPrice]

Could you be more specific because I think it does as I understand it? Maybe we are talking about two different things. This stored procedure is on a database called Rating. It has a USE [Manhattan] in it which is another database on the same server.

USE [Manhattan]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [TekTips]

AS

Declare @String varchar(30)

set @String = (select db_name()) 

select 'Yes you can.  This is on Rating using: ' + @string 
select top 1 * from claim 

GO

Eecuting it on Rating:

execute tektips

displays "Yes you can. This is on Rating using: Manhattan" and one row from the Claim table which do not exist on Rating.

 

[TysonLPrice]

Never mind...I see what you mean (in the stored procedure):

Msg 154, Level 15, State 1, Procedure TekTips, Line 6
a USE database statement is not allowed in a procedure, function or trigger.

 

[djj55]

You can do it through the sp_MSForEachDB command. This is an undocumented command so you will need to to a search to find information. I am not saying this will solve your problem, but...

DECLARE @sqlcode NVARCHAR(2000)
SET @sqlcode = 'IF ''?'' NOT IN (''master'') RETURN; USE ?; -- your code' 
EXECUTE sp_MSForEachDB @sqlcode;

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!