Program06

My First Blog after n years join

I've joined to this forums since 2006 but I'm just *join*; join just as a reader; an active reader but still a passive member.

Starting from now, I try to become more active in writing. My first post is about T-SQL for granting SQL User to only select Table.

declare @ObjectType char(1)

declare @NamaTable varchar(25)

declare @GrantType varchar(15)

declare @UserName char(9)

declare @ExecutedSyntax varchar(1000)

declare @Cursor cursor

set @GrantType = 'SELECT'  -- Bisa diganti dengan 'UPDATE','DELETE', ataupun 'EXECUTE'

set @ObjectType = 'U' -- 'U' untuk User Table dan 'P' untuk StoredProcedure.

set @UserName = 'auditor'

set @Cursor = Cursor FORWARD_ONLY for

Select [name] from sys.objects where type = @ObjectType and is_ms_shipped = 0

open @Cursor

fetch next from @Cursor into @NamaTable

while @@FETCH_STATUS=0

begin

set @ExecutedSyntax = 'GRANT ' + ltrim(rtrim(@GrantType)) + ' ON ' + rtrim(ltrim(@NamaTable)) + ' TO ' + ltrim(rtrim(@userName))

print rtrim(ltrim(@ExecutedSyntax))

fetch next from @Cursor into @NamaTable

end

close @Cursor

deallocate @Cursor

Hope it useful. Should there are more efficient way to do the same task, please share here....  ;-) ting!

Share this post: | | | |