Set permissions on all objects for SQL users

Question

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?

Answer 1

Single line to give permissions at the schema level to a role

GRANT EXECUTE SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO SomeRole

And a second line to add users to the role

EXEC sp_addrolemember 'SomeRole', 'whatever user'

And do this in model so all new databases inherit.

Reasons, you should set permissions once only:

• A schema is a container for objects.
• New objects inherit permissions from the schema
• A role is container for users
• New users are added to a role and inherit

As you've found, migrating or restoring a database can lose object permissions when assigned directly to users. So why put yourself in that position?

You can also CREATE LOGIN with a SID so it is the same on all your servers and you don't get orphaned users either.

If you'd asked the correct question we could have saved you some coding...

Personally and finally, I'd say this is bad practice on "need to have", not "blanket do anything"

• David Mathis: So something like: IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE [type] = 'R' AND [name] = 'ReadOnlyUsers') CREATE ROLE [ReadOnlyUsers] EXEC sp_addrolemember 'db_datareader','ReadOnlyUser' GRANT ?(select? or?) ON SCHEMA::dbo TO [ReadOnlyUsers] GO EXEC sp_addrolemember 'ReadOnlyUsers','ReadOnlyUser' How to differentiate EXEC, UPDATE, DELETE, etc. if all the objects are under dbo though? For example ReadOnlyUser would still need to EXEC stored procs.
• gbn: @David George: schema permissions apply automatically regardless of object: so a proc gets EXEC and a tables gets S/I/U/D. Your GRANT would be GRANT EXEX, SELECT, UPDATE etc ON ...
• David Mathis: so I would run GRANT EXEC, SELECT, UPDATE, INSERT, DELETE on SCHEMA::dbo TO [ReadWriteUsers] Would that allow a that user permission to potentially DROP a procedure then?
• gbn: No, they need ALTER TABLE or such for that