SQL Server Performance Forum – Threads Archive

Script for granting or denying user priv

I’m trying to write a script to make changes to database users access to tables/view, etc. without having to run a GRANT/DENY.. statement for each one. I wrote a cursor to select all of the non system tables from systables and populate a variable that the script would loop through, see below. DECLARE @table AS varchar(50) DECLARE tablelist CURSOR
FOR (SELECT DISTINCT
c.[name] AS tables
FROM dbo.syscolumns AS a
INNER JOIN dbo.systypes AS b ON a.xtype = b.xtype
INNER JOIN dbo.sysobjects AS c ON a.[id] = c.[id]
WHERE LEFT(c.[name],3) <> ‘sys’ ) OPEN tablelist FETCH NEXT FROM tablelist INTO @table
WHILE @@FETCH_STATUS = 0
BEGIN DENY SELECT, INSERT, UPDATE, DELETE
ON @table –<<———–(doesn’t like the variable)
TO rvalleru FETCH NEXT FROM tablelist INTO @table
END
CLOSE tablelist
DEALLOCATE tablelist However this script fails at the @table. If I substitute a single table name then it works. Any ideas on why?

---

You may have to use dynamic SQL here. Try this: exec (‘DENY SELECT, INSERT, UPDATE, DELETE
ON ‘[email protected]+ –<<———–(doesn’t like the variable)
‘ TO rvalleru’)

---

You could also use the fixed database roles, db_datareader, and db_datawriter, which would take care of all these permissions without having to grant them all explicitly. Tom Pullen
DBA, Oxfam GB

---

I would second Tom’s option. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

---

]]>