September 9, 2010 at 7:21 am
Background: An application creates databases under a certain user (let's say Jim). I have to create a script that automatically grants read access to these databases created by Jim to another user Mike.
I have created a stored procedure that builds some dynamic SQL by looping through each database owned by jim and then creating a statement that first issues a sp_grantdbaccess in the current database to Mike and then an 'sp_addrolemember db_datareader, Mike' to grant the read access.
Now each time I run the script there will be some errors because Mike will already have access to some of the databases due to the script having run previously.
The message I get is:
Msg 15023, Level 16, State 1, Line 1
User, group, or role 'Mike' already exists in the current database
I will get this message for each database Mike already has access to.
This is fine as the script still adds Mike to the databases to which he does not have access to.
However when I run the stored procedure as part of a job it will give me the same error, but it will only give me an error for the first database Mike already has access to. None for the rest of the databases Mike already has access to.
It also does not grant Mike access to the databases he does not have access to.
It seems as though the job just stops at the first error, whereas when I run the stored procedure manually it will still continue through the errors until it gets to the database that Mike does not have access to.
Is there anyway for the job to ignore the errors and continue running until it gets to the databases that Mike does not have access to. The script is below:
Create procedure [dbo].[GrantMikeReadAccess]
AS
DECLARE @dbname varchar (50) -- the database name
DECLARE @dbid int -- the databaseid(number)
DECLARE @counter int -- the number of databases owned by coguser
SET @dbid = 1
select @counter = count(*) from master..sysdatabases where suser_sname(sid) = 'Jim'
create table #Jimdb -- this stores all the databases owned by Jim
(dbid int identity,
name varchar (70),
owner varchar (70)
)
insert into #Jimdb select name, suser_sname(sid) as owner from master..sysdatabases where suser_sname(sid) = 'Jim' --populate #Jimdb
WHILE (@dbid <= @counter)
BEGIN
Select @dbname = name from #jimdb where dbid = @dbid
Select
Declare @cmd varchar(8000)
SET @cmd = 'exec '+@dbname+ '..sp_grantdbaccess Mike, Mike
exec '+@dbname+ '..sp_addrolemember db_datareader, Mike
exec (@cmd)
SET @dbid = @dbid + 1
END
drop table #jimdb
September 9, 2010 at 8:22 am
Actually let's simplify it why does the following work when run through query analyzer even when there are failures but stops on the first failure when run as a job.
exec database1..sp_grantdbaccess Mike, Mike
exec database1..sp_addrolemember db_datareader, Mike
exec database2..sp_grantdbaccess Mike, Mike
exec database2..sp_addrolemember db_datareader, Mike
exec database3..sp_grantdbaccess Mike, Mike
exec database3..sp_addrolemember db_datareader, Mike
Thanks
September 9, 2010 at 9:02 am
Try catching the error in the SP.
-Roy
September 9, 2010 at 9:05 am
if your problem not yet resolved then can you just corss check that you getting only the required record in your temp table
----------
Ashish
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply