July 13, 2015 at 11:55 am
The requirements are:
1. the user has read-only permissions to dbo tales.
2. the user can do everything within the rpt schema, which contains all objects analyzing dbo tables.
3. the user does not have any permission outside rpt schema, except permissions in #1.
The current solutions are:
1. grant the user select only on dbo tables.
2. make the user the owner of rpt schema.
3. Grant the user database permission on create table/create procedure/create view/create function.
My question is - in step 3, should I just grant "Alter" database permission to the user? Granting Alter seems to be cleaner and simpler. According to MSDN,
"Alter" confers the ability to change the properties, except ownership, of a particular securable. When granted on a scope, ALTER also bestows the ability to alter, create, or drop any securable that is contained within that scope.
Thanks.
July 13, 2015 at 12:03 pm
The best-practice recommendation is to grant only the specific permissions needed, in this case, I think that would be:
GRANT SELECT ON SCHEMA::dbo TO [<user_name>];
GRANT CONTROL ON SCHEMA::rpt TO [<user_name>];
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 14, 2015 at 9:28 am
Scott,
My test showed that , without the grant on create table/create procedure/create view/create function, the user cannot create any objects under the rpt schema.
I also tested "Grant Alter to the user", which appears to granting too much permissions. After the "Grant Alter", the user had the permission to create a view in dbo schema, which was not what I want.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply