Need t-sql script to add users to grant users access to db

I need help on writing script to grant users from master..sysxlogins access to [some database name]. I am brand new to sql and do not know how to write script to "loop" through results of select statement.

I AM USING SQL SERVER 2000

Something like
-LOCAL VARIABLES
delcare @username varchar(30)
-SELECT
select * from master..sysxlogins where left(name,7) =   'PSIUser'
-UPDATE
begin
  set @username = [stumped here'
  USE [some databasename]  EXEC sp_grantdbaccess +  @username
end
nussedogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BrandonGalderisiCommented:
Choose the grant line you want.
Something like
-LOCAL VARIABLES
delcare @username varchar(30)
 
declare pt_grantCursor cursor for
select name from master..syslogins 
where isntuser=0
and left(name,7) =   'PSIUser'
open pt_grantCursor
fetch next from pt_GrantCursor into @username
while @@fetch_status=0
begin
--this
      exec sp_grantdbaccess @userName
-- or this
      exec sp_msforeachdb 'exec ?..sp_grantdbaccess [' + @userName +']'
 
fetch next from pt_GrantCursor into @username
end
close pt_grantCursor
deallocate pt_grantCursor

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
declare @username varchar(30), @cnt INT
DECLARE @users TABLE ( i INT IDENTITY, userName VARCHAR(30) )
INSERT INTO @users
select name from master..sysxlogins where left(name,7) =   'PSIUser'
SELECT @cnt  = @@ROWCOUNT

WHILE @cnt <> 0
begin
  select @username = userName FROM @users WHERE i = @cnt
  EXEC ( 'USE [some databasename] ; EXEC sp_grantdbaccess ''' +  @username +'''' )
  SELECT @cnt = @cnt - 1
end
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.