Impersonate login credentials - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Impersonate login credentials

Author

Topic

kalwar
Starting Member

3 Posts

Posted - 2011-09-21 : 13:29:06

Hello all,
I am trying to create a new login and authorization the login to a database through stored procedure. But I get 'User does not have permission to perform this action.' error. I am able to execute the CREATE LOGIN and ALTER AUTHORIZATION commands in a SQL Query window. I am using the same Login user name to execute the procedure and SQL Query Window. The user has security Admin privilege.

Here is the code.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_Test1] Script Date: 09/21/2011 08:50:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Test1](@dbname varchar(256), @User varchar(56))
WITH EXECUTE as 'EPRestoreAdmin' --EXECUTE as USER='EPRestoreAdmin';
AS
declare @sql nvarchar(3000);
-- TODO: restore the users
IF NOT EXISTS (SELECT * FROM master.sys.syslogins WHERE name = @User)
Begin
SET @sql = ' CREATE LOGIN [' + @User +'] WITH PASSWORD=''1'+ @dbname + '@password'',' + 'DEFAULT_DATABASE = [' + @dbname + '];';
-- select @sql;
execute (@sql) as user='EPRestoreAdmin';
end
SELECT @sql='ALTER AUTHORIZATION on DATABASE::'+ @dbname + ' to '+@User+';'
execute(@sql)as user='EPRestoreAdmin';
-- SELECT @sql= 'USE '+ @dbname +';'

--Add the new logins to the new database
set @sql='CREATE USER '+@User+ ' FOR LOGIN [' + @user + '];';
execute (@sql) as user='EPRestoreAdmin';
--CREATE USER SQLDBA FOR LOGIN [SQLSRV90\SQLDBA]

--Add 'vistaua' local account to the db_owner role
EXEC sp_addrolemember 'db_owner', @User ;
select 'Restore Successful' [Result];

Does anyone know why it is not recognizing the IMPERSONTE login credentials within a stored procedure?

Thank you for all the helps in advance.

Regards,
Kumar.

KAA

kalwar
Starting Member

3 Posts

Posted - 2011-09-21 : 20:41:55

It looks like the EXEC within the procedure is breaking the IMPERSONATE chain. I might have to explore the certification method.

KAA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources