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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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