Using Sp_grantdbaccess

i have a stored procedure for creating SQL server users within a database but i have been given a change request where the created SQLServer user will need to be granteddbaccess to another db also

is there a way to do this??

at the mo my sp reads like this:-





Code Block

CREATE PROCEDURE ys_InsertUser2
@int_Identity int OUTPUT,
@str_username varchar(25),
@str_role varchar(20),
@str_password varchar(15),
@str_Database varchar(20)
AS

SET NOCOUNT ON
DECLARE @USER varchar(25)
SELECT @USER=username from tblUser where username=@str_username
BEGIN
if not exists (select * from master..syslogins where name = @str_username)
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132)

select @logindb = @str_Database, @loginlang = N'British'

if @logindb is null or not exists (select * from master..sysdatabases where name = @logindb)
select @logindb = N'master'

if @loginlang is null or (not exists (select * from master..syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language

exec sp_addlogin @str_username, null, @logindb, @loginlang
END
if not exists (select * from sysusers where name = @str_username and uid < 16382)
EXEC sp_grantdbaccess @str_username, @str_username
exec sp_addrolemember @str_Role, @str_username
EXEC sp_password NULL, @str_password, @str_username
END

SELECT @int_Identity = 0 --just a bluff inorder to use formclasses
IF @@Error = 0
BEGIN
RETURN 0
END
ELSE
BEGIN
RETURN 99
END

SET NOCOUNT OFF





but i need to expand the sp_grantdbaccess and sp_addrolemember to include access to another DB and not the local one which the stored proc is running in.

is this possible?

Cheers,
Craig