January 4, 2011 at 5:19 pm
Hi,
I created a login LSCOTT and mapped to a database MYDB
Created a schema SHSCOTT and selected LSCOTT as schema owner (please see the attachment)
Connect to SSMS as LSCOTT and trying to create table, view but it's giving me permission errors.
Then I given the create table, create view, create procedure permission to LSCOTT
grant create table to LSCOTT;
grant create procedure to LSCOTT;
grant create view to LSCOTT;
Question:
Why we need to grant permission to LSCOTT to create each object even after making that login LSCOTT as the schema owner while it got created?
Thanks
January 6, 2011 at 3:21 pm
I suspect the answer is in 'http://msdn.microsoft.com/en-us/library/ms191291(v=SQL.100).aspx' with the key extract:
'Because the SQL Server security model is hierarchical, CONTROL at a particular scope implicitly includes CONTROL on all the securables under that scope.'
The schema is under Database, so although you have Control of the schema you still need the below permissions on the database in order to take advantage of the schema permissions you have as an owner.
January 7, 2011 at 11:59 am
I have created a schema in SQL Server 2008 and some tables, views and procedures in that schema and able to see that schema in Security folder.
But unable to see what are all the objects that schema has in it when select the schema under security folder. Where can I see what objects a particular schema has in it?
Thanks
January 7, 2011 at 3:29 pm
There does not appear to be a GUI option in SSMS see all objects which belong to a schema.
As a work around, possibly you could use a query as below in the database to get this information:
SELECT SCHEMA_NAME(schema_id) as schema_check,* FROM sys.objects
WHERE schema_id = SCHEMA_ID('SHSCOTT')
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply