I have a procedure that loops through all objects in the database and assigns them proper permissions to that object. I want to know if there is a better way to do this? I use a model database to create new databases so I have to run this every time I create a new database. Here is an idea of what it looks like (note: there is a chunk missing from the beginning that drops all users and recreates the necessary users; this is necessary since the SID's change):
CREATE PROCEDURE usp_SetPermissions AS BEGIN DECLARE @CurrentId INT DECLARE @ObjectName NVARCHAR(128) DECLARE @Message NVARCHAR(160) DECLARE @Error INT DECLARE @Sql NVARCHAR(256) CREATE TABLE #tmpDbObjects ( ID INT IDENTITY(1,1), ObjectName NVARCHAR(128), Completed BIT ) INSERT #tmpDbObjects(ObjectName, Completed) SELECT DISTINCT [Name], 0 As Completed FROM sys.objects WHERE [type] = 'U' AND is_ms_shipped <> 1 WHILE EXISTS (SELECT 1 FROM #tmpDbObjects) BEGIN -- Pick first uncompleted object SELECT TOP 1 @CurrentId = ID, @ObjectName = ObjectName FROM #tmpDbObjects -- Grant permissions to DB user SET @Sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) EXEC sp_sqlexec @Sql -- Update object completion DELETE #tmpDbObjects WHERE [Id] = @CurrentId -- Clear variables SET @Sql = NULL SET @CurrentId = NULL END INSERT #tmpDbObjects(ObjectName, Completed) SELECT DISTINCT [Name], 0 As Completed FROM sys.objects WHERE [type] = 'P' AND is_ms_shipped <> 1 WHILE EXISTS (SELECT 1 FROM #tmpDbObjects) BEGIN -- Pick first uncompleted object SELECT TOP 1 @CurrentId = ID, @ObjectName = ObjectName FROM #tmpDbObjects -- Grant permissions to DB user SET @Sql = 'GRANT EXEC ON dbo.' + QUOTENAME(@ObjectName) + ' TO ' + QUOTENAME(DB_NAME()) EXEC sp_sqlexec @Sql -- Update object completion DELETE #tmpDbObjects WHERE [Id] = @CurrentId -- Clear variables SET @Sql = NULL SET @CurrentId = NULL END This scripts continues on to do similar things for all the views, functions, etc. in the database. Any ideas for speeding this thing up or is there a better way to do this?
