Granting Permission on Databases and Schemas I have a  Security permissions script i have inherited that i'd like to improve. The script grants permissions on the user databases and Schemas.
I'd like to improve the script by  iterating through all the schemas instead of hard coding the schemas like i did within the cursor. 
Any thought?

**

-- Assigning Permission to 'Domain\Development' at a DAtabase level 
DECLARE @command nvarchar(max), @usr sysname, @DB nvarchar(500)
DECLARE @loginName varchar(256)
SET @loginName ='Domain\Development'
DECLARE Devcur CURSOR FOR 
SELECT NAME FROM sys.databases WHERE database_id&gt;4 AND NAME &lt;&gt;LOOKUP'
                                                   AND NAME  NOT LIKE  '%ReportServer%'
                                                   AND NAME NOT LIKE '%DBAdmin%'
                                                   AND NAME NOT LIKE '%DBA%'
                                                   AND state_desc='ONLINE' AND is_read_only=0
OPEN Dev
FETCH NEXT FROM Devcur INTO  @DB
WHILE @@FETCH_STATUS = 0
BEGIN
--Granting Access to User databases 
BEGIN
    SET @usr='['+@loginName+']'
    SELECT @command = 'Use ' + @DB + ';
    EXEC sp_addrolemember N''db_datareader'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+'''; 
    EXEC sp_addrolemember N''db_ddladmin'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+'''; 
    EXEC sp_addrolemember N''db_datawriter'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+'''; 
    EXEC sp_addrolemember N''db_owner'',N'''+REPLACE(REPLACE(@usr, '[', ''), ']', '')+''';
    GRANT Showplan TO '+@usr+';
    GRANT EXECUTE TO '+@usr+';
    GRANT VIEW DEFINITION TO '+@usr+';
    GRANT CONNECT TO '+@usr+';
    GRANT CREATE SCHEMA TO '+@usr+';
    GRANT DELETE TO  '+@usr+';
    GRANT INSERT TO  '+@usr+';
    GRANT UPDATE TO  '+@usr+';'

    PRINT @command
    Print '-----------------BUILD-----------------'
    Print @DB
--EXEC SP_executeSQL @command
END
--Access SOMER schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''SOMER'') &gt;= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + '''; 
    GRANT VIEW DEFINITION ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[SOMER] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[SOMER] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
--Access Poser schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''Lser'') &gt;= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + '''; 
    GRANT VIEW DEFINITION ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[Poser] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[Poser] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
--Access Lser schema
SET @usr='['+@loginName+']'
SELECT @command = 'Use ' + @DB + ';
IF (select Count(*) from sys.schemas where name = ''Lser'') &gt;= 1
BEGIN
EXEC sp_droprolemember N''db_denydatawriter'', N''' + REPLACE(REPLACE(@usr, '[', ''), ']', '') + '''; 
    GRANT VIEW DEFINITION ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT DELETE ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT INSERT ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT UPDATE ON SCHEMA::[Lser] TO ' + @usr + ';
    GRANT EXECUTE ON SCHEMA::[Lser] TO ' + @usr + ';
End;'
--PRINT @command
EXEC SP_executeSQL @command
FETCH NEXT FROM Dev INTO  @DB
END
CLOSE Dev
DEALLOCATE Dev
GO


**

Would very much appreciate.
Your script is assigning the user to the db_owner role.

All other permissions being applied are therefore redundant as this implies ALL of those other permissions.