June 5, 2013 at 2:39 pm
Would granting control at the schema level take the place of granting individual permissions on the schema? For example, GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
Verses
--apply permissions to schemas
GRANT ALTER ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT CONTROL ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT INSERT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT DELETE ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT UPDATE ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
--ensure role membership is correct
EXEC sp_addrolemember N'Developer_Role ', N'User1'
GO
--allow users to create tables in Developer_Schema
GRANT CREATE TABLE TO [Developer_Role]
GO
--Allow user to connect to database
GRANT CONNECT TO [User1]
Thanks, Kevin
June 10, 2013 at 9:58 am
BOL -
Object owners can grant permissions on the objects they own. Principals with CONTROL permission on a securable can grant permission on that securable.
Grantees of CONTROL permission on a schema can grant any permission on any object within the schema.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
June 10, 2013 at 12:45 pm
Thanks Ness. So, I guess "Principals with CONTROL permission on a securable can grant permission on that securable.
Grantees of CONTROL permission on a schema can grant any permission on any object within the schema" means that principals can ONLY grant permissions on securables but not access the securable, for example, with a Select, Insert, Delete or Update Statement. Hence, the reason for having to apply the individual permissions for Select, Insert, Delete and Update Statements:
GRANT SELECT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT INSERT ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT DELETE ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
GRANT UPDATE ON SCHEMA::[Developer_Schema] TO [Developer_Role]
GO
I will have to read up more on this topic.
Thanks, Kevin
March 28, 2022 at 1:37 pm
Hi all,
We have an issue on a database that any newly created Login/its User gets automatically below permissions, sees a lot more than what the objects in the Public role added...
I created a new user for this test with in [MyDB] and it automatically sees /have all below permissions when no explicit permissions granted for all below /no roles to the user 'testtest'' (except that it is a member of the Public role which has 14 user objects permissions in it)?
Can this be reverted ?
Any help would be appreciated here.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy