USE MyDB
GO

exec sp_dropuser 'Userid'
exec sp_grantdbaccess 'Userid','Userid'
exec sp_addrolemember 'db_owner','Userid'

declare @objname varchar(100)
    
declare namelist  insensitive cursor for
select name
  from [MyDB].[dbo].sysobjects
 where xtype in('p','fn') AND category=0
     
open namelist
fetch next from namelist into @objname
while @@fetch_status=0
begin
	exec('grant execute on [dbo].[' + @objname + '] to [Userid]')
	fetch next from namelist into @objname
end
     
close namelist
deallocate namelist

quote:

---

Originally posted by tduggan

You should be calling sp_ with the three part naming convention so that you don't require USE:

SomeDBName.dbo.sp_grantdbaccess

Tara

---

EXEC master.dbo.isp_MyDB_Restore_Last_Night_Backup

USE MyDB
GO
EXEC master.dbo.isp_MyDB_Grant_Rights

quote:

---

Originally posted by tduggan

I would put the grants in a stored procedure. Then call the stored procedure with DBName.dbo.SPName. Or you could use sp_executesql and qualify it with the database name instead of using EXEC.

Tara

---

quote:

---

Originally posted by tduggan

I think you are going to need to put isp_MyDB_Grant_Rights into MyDB. You shouldn't be putting anything into master anyway. If you want objects outside of your user database, you should be creating a new database for this. We call ours Admin.

Tara

---