problem revoking a users'' access to msdb

Question

Post reply

problem revoking a users'' access to msdb

Marcus Malinow

SSC Veteran

Points: 264
More actions
April 11, 2007 at 10:10 am

#370484

Hi,
I'm trying to fix a user permissions related stored procedure in my application which is causing me a few problems.
Most of my users only have access to my application database, thus when I need to delete these users I do a sp_revokedbaccess followed by a sp_droplogin.
The problem occurs with some of the admin users. These users need to be able to trigger jobs, so they have access to msdb. Unfortunately this means that when I try to delete them their permissions are successfully revoked from the application database, but the sp_droplogin fails as they still have permissions on msdb. I've tried amending my stored procedure by adding 'use msdb' followed by a call to sp_revokedbaccess, but it fails as the USE statement isn't allowed within the context of a stored procedure.
Any ideas?
thanks,
Marcus

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

You could use dynamic sql in your stored procedure. It would look sort of like this:

declare @SqlCmd nvarchar(4000)

set @SqlCmd = N'use msdb;exec your procedure'

exec @SqlCmd

hth

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

If there's any chance the user is in other databases you could check them all:

EXEC

master.dbo.sp_MSforeachdb REPLACE('USE [?]; IF EXISTS(SELECT NULL FROM sysusers WHERE NAME=''<user>'') EXEC sp_dropuser ''<user>''', '<user>', @user)

Marcus Malinow SSC Veteran Points: 264 More actions · Answer 3

thanks for the help on this!

I think the way to go is dynamic sql (why didn't I think of that!). This has uncovered a couple of issues with user permissions in my server though (admin users don't have sufficient permissions to revoke access to msdb!).