drop schema JiwaLogin drop schema JiwaReports drop schema JiwaUser GO drop user JiwaLogin drop user JiwaReports drop user JiwaUser GO create user JiwaLogin create user JiwaReports create user JiwaUser GO PRINT 'GRANTing ALL to all USER tables' PRINT '' DECLARE @tablename varchar(500) DECLARE @tablename_header varchar(500) DECLARE @ObjectType VARChar(2) DECLARE tnames_cursor CURSOR FOR SELECT sys.objects.name, sys.objects.type FROM sys.objects JOIN sys.schemas ON sys.schemas.schema_id = sys.objects.schema_id WHERE (sys.objects.type = 'U' OR sys.objects.type = 'V' OR sys.objects.type = 'P' OR sys.objects.type = 'FN') AND sys.schemas.name = 'dbo' ORDER BY sys.objects.Name OPEN tnames_cursor FETCH NEXT FROM tnames_cursor INTO @tablename, @ObjectType WHILE (@@FETCH_STATUS <> -1) BEGIN /* Because @@FETCH_STATUS will return one of three values, -2, -1, or 0, all three cases must be tested. In this case, if a table has been dropped since the time this stored procedure was executed, it will be skipped. A successful fetch (0) will cause the GRANT within the BEGIN..END loop to execute. */ IF (@@FETCH_STATUS <> -2) BEGIN SELECT @tablename_header = 'Granting To --> ' + RTRIM(UPPER(@tablename)) PRINT @tablename_header IF @ObjectType = 'P' OR @ObjectType = 'FN' BEGIN EXEC('GRANT EXECUTE ON [' + @tablename + '] TO JiwaUser') EXEC('GRANT EXECUTE ON [' + @tablename + '] TO JiwaReports') END ELSE BEGIN EXEC('GRANT SELECT, INSERT, UPDATE, DELETE ON [' + @tablename + '] TO JiwaUser') EXEC('GRANT SELECT ON [' + @tablename + '] TO JiwaReports') END END FETCH NEXT FROM tnames_cursor INTO @tablename, @ObjectType END GRANT SELECT ON HR_Staff TO JiwaLogin GRANT INSERT ON SY_LoginAudit To JiwaLogin PRINT '' SELECT @tablename_header = '************* NO MORE TABLES' + ' *************' PRINT @tablename_header PRINT '' PRINT 'GRANT has been run against all user-defined tables.' DEALLOCATE tnames_cursor GO ----Password reset to known working database login details update hr_staff set SQLLogin = (SELECT TOP 1 SQLLogin FROM JiwaDemo.dbo.HR_Staff) update hr_staff set SQLPassword = (SELECT TOP 1 SQLPassword FROM JiwaDemo.dbo.HR_Staff) update hr_staff set ReportSQLLogin = (SELECT TOP 1 ReportSQLLogin FROM JiwaDemo.dbo.HR_Staff) update hr_staff set ReportSQLPassword = (SELECT TOP 1 ReportSQLPassword FROM JiwaDemo.dbo.HR_Staff) update hr_staff set SQLLoginLastSavedDateTime = (SELECT TOP 1 SQLLoginLastSavedDateTime FROM JiwaDemo.dbo.HR_Staff) update hr_staff set ReportsLoginLastSavedDateTime = (SELECT TOP 1 ReportsLoginLastSavedDateTime FROM JiwaDemo.dbo.HR_Staff) GO