Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Grant Control On Schema verses granting individual permissions on schema? Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 2:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:14 PM
Points: 311, Visits: 323
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



Post #1460452
Posted Monday, June 10, 2013 9:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:04 AM
Points: 179, Visits: 814
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
Post #1461637
Posted Monday, June 10, 2013 12:45 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:14 PM
Points: 311, Visits: 323
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



Post #1461719
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse