Need help for t-sql script findout for base tables for created view

• vijaysoperla

  SSC Journeyman

  Points: 81

  More actions

  December 14, 2009 at 1:53 am

  #137669

  I need to give grant permission for views. but i need give grant permission for base tables also. i need t-sql script for findout base table perticuler database and give grant (select) permission for single user

• colin.Leversuch-Roberts

  SSC Guru

  Points: 52643

  More actions

  January 11, 2010 at 6:42 am

  #1101744

  this will generate a set of statement to run in QA ( allows you to be selective if required ), change "Users" to the required user/logon/role

  select 'grant select on ['+name+'] to Users' from sysobjects where type ='U' ;

  this does a blanket grant on everything ( modify as required )

  DECLARE @SQLCmdVARCHAR(1000)

  DECLARE GrantExecCrs SCROLL CURSOR FOR

  SELECT 'GRANT select ON ' + Name + ' TO users'

  FROM sysobjects

  WHERE TYPE = 'U'

  OPEN GrantExecCrs

  FETCH NEXT FROM GrantExecCrs INTO @SQLCmd

  WHILE @@FETCH_STATUS = 0

  BEGIN

  --SELECT @SQLCmd

  EXEC (@SQLCmd)

  FETCH NEXT FROM GrantExecCrs INTO @SQLCmd

  END

  CLOSE GrantExecCrs

  DEALLOCATE GrantExecCrs

  GO

  [font="Comic Sans MS"]The GrumpyOldDBA[/font]
  www.grumpyolddba.co.uk
  http://sqlblogcasts.com/blogs/grumpyolddba/