 |
 |
|
|
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'idr_BusinessViewUser' AND type = 'R')
CREATE ROLE [idr_BusinessViewUser] AUTHORIZATION [dbo]
/****** Apply execute & select permissions to DatabaseRole [idr_User] Script Date: 05/30/2007 08:50:00 ******/
DECLARE @SQLString NVARCHAR(1000)
, @ViewName NVARCHAR(256)
, @ReturnCode INT
DECLARE CurViews CURSOR FOR
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME LIKE 'ibv%' OR
TABLE_NAME LIKE 'icv%' OR
TABLE_NAME LIKE 'idv%'
FOR READ ONLY
OPEN CurViews
FETCH NEXT FROM CurViews INTO @ViewName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLString = 'GRANT SELECT ON ' + @ViewName + ' TO idr_BusinessViewUser'
EXECUTE @ReturnCode = sp_executesql @SQLString
FETCH NEXT FROM CurViews INTO @ViewName
END
CLOSE CurViews
DEALLOCATE CurViews
GO