November 17, 2013 at 9:07 am
hello 🙂
please help me. i am not DB admin, and have no idea how to solve my problem: in a DB i have 2 schemas: A and B. B schema's procedures use A schema's procedures. so I have to create user MyUser that will not be able to see A schema, but if the one uses B schema's procedures that use A's procedures - there must not be any problems...
please tell me step by step how to do this, or may be script is just needed??
November 17, 2013 at 9:25 am
You'll need to make sure that the owner of both schemas and the sprocs being referenced is the same (e.g. 'dbo'). Then you don't have to do anything special other than allow access to schema B (assuming there's no specific DENY permission set).
Example: GRANT EXECUTE ON SCHEMA::B TO Access_Schema_B; (see below, why I didn't use 'MyUser')
However, I would recommend to add a role (e.g. 'Access_Schema_B') and assign the permissions to the role. Therewith, when you need to give another user the same perission, you'll simply add this user to the role and you're all set.
November 17, 2013 at 9:33 am
and what about A schema? Do I have to deny access to the role ?
sorry, but i have no experience in DB administration and user's permissions ... :blush:
November 17, 2013 at 9:41 am
You must not be any specific permission for this user (or role) for Schema A. Otherwise the "least privilege" concept would apply and you might not be able to call the sprocs in that schema. So, for schema A neither the user nor the role should have any specific permission set, being it GRANT or DENY permissions.
November 18, 2013 at 12:29 am
something like this?
CREATE USER testuser WITH PASSWORD = 'Aa123456'
GRANT EXECUTE ON Schema:: B TO testuser
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply