help using sp_helprotect - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help using sp_helprotect

Author

Topic

rtutus
Aged Yak Warrior

522 Posts

Posted - 2007-03-04 : 14:24:41

Hi I use exec helprotect to put a user's perimssion into a temp table called #permissions defined with these columns : (DBname,[Owner] ,[Object],[Grantee] , [Grantor] , [ProtectType] , [Action] , [Col] ) like this:

Now when I get those permissions I use this script inside a cursor to replicate each permission to the user: @newLoginParam

My question: is there any big business scenario in which my script might fail or miss some permissions for any user?

here is the main part of my script:

declare @objj as varchar(8000)
declare @grantee as varchar(8000)
declare @action as varchar(8000)
declare @col as varchar(8000)
declare @sqlGrant as varchar(8000)
declare @ProtectType as varchar(8000)
declare @count as int
set @count=0
declare crsMyTblPermis cursor for
select Object,Grantee,ProtectType , Action, Col from #permissions
open crsMyTblPermis
fetch next from crsMyTblPermis into @objj, @grantee, @ProtectType, @action,@col
while @@fetch_status=0
begin

if (@objj!='.' and (@col = '.' OR @col = '(All+New)'))
begin
set @sqlGrant = @ProtectType +@action + ' on ' + @objj + ' to ' + @newLoginParam
end

if (@objj!='.' and @col != '.' and @col != '(All+New)')
begin
set @sqlGrant = @ProtectType +@action + ' on ' + @objj + '(' + @col + ') to ' + @newLoginParam
end

if (@objj='.')
begin
set @sqlGrant = @ProtectType +@action + ' to ' + @newLoginParam
end

exec(@sqlGrant)
fetch next from........

...etc

P.S: The main reason is that I can t test my script now on the production platform on which the script will be run

Thanks for your advice .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources