Securing access to SQL Instance

Question

Securing access to SQL Instance

Duncan Lawrence

SSCrazy

Points: 2047
More actions
August 21, 2023 at 8:15 am

#4266954

Hi, I have a need to harden-down users access to a SQL Server instance.
I have a number of SQL authenticated accounts that need to be able to create and curate database objects on a dedicated schema.
At the moment they have membership of the ddl_admin role on the database, however this is proving insufficient as, for example, they cannot grant permissions on the new objects they create.
What I would like is to allow these SQL accounts to expose and harvest code from any database object on schemas other that the dedicated schema. But they should not be able to create, alter or delete, any object on these other schemas.
On the dedicated schema, I want them to be able to create, alter and delete any object on that schema.
Is this possible? And, if so, how?
Thank you in advance for any advice you have.
All the best,
Duncan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

After booting them out of the ddl_admin role, you can GRANT and DENY permissions to them directly on the Schema, which will carry through all objects within the schema. Include the WITH GRANT OPTION switch to allow those users to grant permissions to the objects they create.

Ex: GRANT CONTROL ON SCHEMA:: <your_custom_schema> TO [<user_or_group_or_role>] WITH GRANT OPTION;

Note: The example above is the least secure but most direct method to solve your issue. Read the docs (linked below) for security caveats to ensure you're not opening things up more than you need to.

MS Doc: https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver15

Eddie Wuerch
MCM: SQL

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 2

Thanks Eddie I appreciate you responding.

All the best, Duncan

All the best,

Duncan

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

GRANT WITH GRANT

What?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

Grant Fritchey wrote:

GRANT WITH GRANT
What?

Shouldn't that be 'GRANT WITH CALL GRANT' ???

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs