SET @Login_Name = #temp.loginname

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)

SET @Login_Name = #temp.loginname

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-07-15 : 09:40:34

I have the following script where i would like to make some changes and need some assistance.
the @login_name is declared and then you see the
SET @Login_Name = N'xbbjvk6' all the users are hardcoded in here and
i would like to insert the users from the table to a temp table.
this is where my confusion lies.(see in red at bottom)
----Variable declaration
DECLARE @Login_Name AS NVARCHAR(20)

---- Assign values to variables
SET @Login_Name = N'xbbjvk6' -- Naresh Agarwal

---- Drop user from dataabse
EXEC sp_revokedbaccess @Login_Name

---- create user in database
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name

---- Assign roles to user
EXEC sp_addrolemember N'db_owner', @Login_Name

EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

USE EnSIS_Daily_Refresh
GO

---- Variable declaration
DECLARE @Login_Name AS NVARCHAR(20)

---- Assign values to variables
SET @Login_Name = N'ADCXSV8' -- Vincent

---- Drop user from dataabse
--EXEC sp_revokedbaccess @Login_Name

---- create user in database
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name

---- Assign roles to user
EXEC sp_addrolemember N'db_owner', @Login_Name

EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

USE EnSIS_Daily_Refresh
GO
---- Variable declaration
DECLARE @Login_Name AS NVARCHAR(20)

---- Assign values to variables
SET @Login_Name = N'XECCSR4' -- Kieran

---- Drop user from dataabse
EXEC sp_revokedbaccess @Login_Name

---- create user in database
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name

---- Assign roles to user
EXEC sp_addrolemember N'db_owner', @Login_Name

EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

--------------------------------------------
USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'ADCXZQ7' -- Martin Fealy
---- Drop user from dataabse
--EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

--------------------------------------------
USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'WNTLON02.RSamuel' -- Robert Samuel
--EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

--USE [EnSIS_Daily_Refresh]
--GO
--EXEC dbo.sp_grantdbaccess @loginame = N'RSamuel', @name_in_db = N'RSamuel'
--USE [EnSIS_Daily_Refresh]
--EXEC sp_addrolemember N'db_owner', N'RSamuel'
--USE [EnSIS_Daily_Refresh]
--EXEC sp_addrolemember N'Internal_User_Access', N'RSamuel'
--update UserPermission set RoleCode = 'ITA' where UserCode = 'rs1'
--GO

--------------------------------------------
--USE EnSIS_Daily_Refresh
--GO
--DECLARE @Login_Name AS NVARCHAR(20)
--SET @Login_Name = N'PEUR.PBrophy' -- Paul Brophy
--EXEC sp_revokedbaccess @Login_Name
--EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
--EXEC sp_addrolemember N'db_owner', @Login_Name
--EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
--Go

--------------------------------------------
USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'XECCSMT' -- Paul Brophy
EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

--------------------------------------------
--USE EnSIS_Daily_Refresh
--GO
--DECLARE @Login_Name AS NVARCHAR(20)
--SET @Login_Name = N'VJuneja' -- Vishal Juneja
--EXEC sp_revokedbaccess @Login_Name
--EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
--EXEC sp_addrolemember N'db_owner', @Login_Name
--EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
--GO

---------------------------------------------------------------------------

--USE EnSIS_Daily_Refresh
--GO
--DECLARE @Login_Name AS NVARCHAR(20)
--SET @Login_Name = N'PEUR.BKELLY' -- Bebhinn Kelly
--EXEC sp_revokedbaccess @Login_Name
--EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
--EXEC sp_addrolemember N'db_owner', @Login_Name
--EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
--GO

---------------------------------------------------------------------------

--USE EnSIS_Daily_Refresh
--GO
--DECLARE @Login_Name AS NVARCHAR(20)
--SET @Login_Name = N'ADCXZ6F' -- Adam Gray
--EXEC sp_revokedbaccess @Login_Name
--EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
--EXEC sp_addrolemember N'db_owner', @Login_Name
--EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
--GO

---------------------------------------------------------------------------

USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'XBBJ22M' -- Aravind Mondrety
EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

---------------------------------------------------------------------------

USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'XECCPCS' -- Dave Kavanagh
EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

---------------------------------------------------------------------------

USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'XECCPC7' -- James J Douglas
EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO
******************************
im not sure how to have the
SET @Login_Name = #temp.loginname ?????

DECLARE @Login_Name AS NVARCHAR(20)

SELECT
usercode,
name,
sqluserid as loginname
into #temp
from Users
where ActiveFlag ='Y' and UserInactive='N'
order by name

---- Assign values to variables
SET @Login_Name = #temp.loginname

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-15 : 10:01:26

SET @Login_Name = (select loginname from #temp)

This part (select loginname from #temp) can only return one value or it will break.

Jim

Everyday I learn something that somebody else already knew

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-07-15 : 10:09:38

how do i get it to return all names
or have it go to the next name in a 2nd query listed below it

if you look furthur down the the script you will notice
the following where is picks up another user id

USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'XBBJ22M' -- Aravind Mondrety
EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

---------------------------------------------------------------------------

USE EnSIS_Daily_Refresh
GO
DECLARE @Login_Name AS NVARCHAR(20)
SET @Login_Name = N'XECCPCS' -- Dave Kavanagh
EXEC sp_revokedbaccess @Login_Name
EXEC dbo.sp_grantdbaccess @loginame = @Login_Name, @name_in_db = @Login_Name
EXEC sp_addrolemember N'db_owner', @Login_Name
EXEC sp_addrolemember N'Internal_User_Access', @Login_Name
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources