• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1711
  • Last Modified:

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
0
nussedog
Asked:
nussedog
1 Solution
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now