Using Grant command with variable.

[EM1107]

I am trying to change the rights of the tables in my databas. To do this I would like to use a cursor that look for the tables name and grant the proper permissions to all the tables. The problem I have at this time is that all my script does at this time is to print me the information.

I have attatch a copy of my script and I would like if anyone have a suggestion to my problem.

---------------------------------------------------------
DECLARE @UID INT
SET @UID = (SELECT UID FROM SYSUSERS WHERE NAME = 'test')

DECLARE @TBName VARCHAR (255)
DECLARE @SQL varchar (255)
DECLARE @DBOWNER VARCHAR (255)

set @DBOWNER = 'test'
DECLARE TableCursor CURSOR FOR
SELECT NAME FROM SYSOBJECTS WHERE UID = @UID
DECLARE @Command VARCHAR (255)

OPEN TableCursor
FETCH NEXT FROM TABLECURSOR INTO @TBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Changing Owner of ' + (@DBOWNER) +'.' + (@tbname)
set @SQL = 'GRANT REFERENCES, SELECT, UPDATE, INSERT, DELETE ON ' + @DBOWNER +'.' + @tbname
SELECT @sql = @sql + ' TO John, Mary, Mark'
FETCH NEXT FROM TABLECURSOR INTO @TBNAME
END
CLOSE TABLECURSOR
DEALLOCATE TABLECURSOR

----------------------------------------------------------

Thanks!

 

[RobertT687]

I believe it is as simple as adding:
EXECUTE (@sql)
after the @sql variable has been completely built up.

 

[tlbroadbent]

FYI: The undocumented stored procedure sp_msforeachtable provides an alternative method for performing opeerations against all tables in a database. The following script performs the same function as EM1107's script.

Declare @sql varchar(100)
Select @sql=
'IF Left(''?'',5)=''[test]''' +
' BEGIN PRINT ''?'' ;' +
' GRANT ALL ON ? TO mary,john,mark ; END'

exec sp_msforeachtable @sql

NOTE: In this example I used GRANT ALL rather than enumerating the permissions to grant. When all permissions are to be granted this is a helpful shortcut. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.