How to assign appropriate permissions for different groups of users in SQL Server if the built-in server roles do not meet your need?

The nine server roles in SQL Server 2005/08/R2 are fixed and inflexible. If they are not sufficient to meet your needs, you can use the flexible SQL Server login and user-defined database role. For instance, you can create a "Developer" database role for a database in SQL 2008. The role should be allowed to read anything, and view the definitions of SPs/Functions, etc; but you do not want them to be able to alter SPs/Tables, etc.

Use [iDBA]

GO

--Step 1 - create a SQL Server login

Create LOGIN [ClarkKent] WITH PASSWORD = 'NotTheRealPassword'

--Step 2 - create a Database user for the login

Create USER [ClarkKent] FOR LOGIN [ClarkKent]

--Step 3: Create a user-defined database role

CREATE ROLE [MyDevelopers]

--Step 4 - Add the user-defined database role to the fixed database role

--allow the users to read data

EXEC sp_addrolemember   N'db_datareader',

                                    N'MyDevelopers'

--Step 5 - assign additional permissions to the database role

--allow the users to see view proc and function definitions

Grant View Definition ON SCHEMA :: [dbo] To [MyDevelopers]

-- Step 6: finally add the user to the role:

EXEC sp_addrolemember   N'MyDevelopers',

                                    N'ClarkKent'

--step 7: test:

EXECUTE AS USER = 'ClarkKent'

--who am i?

SELECT

      SUSER_NAME()

--do stuff

--change back into superman

REVERT;

--clean up after ourselves

/*

DROP ROLE [MyDevelopers]

DROP USER [ClarkKent]

DROP LOGIN [ClarkKent]

*/

see http://www.sqlservercentral.com/Forums/Topic1259555-1526-1.aspx#bm1259574 for the original post.