How to stop a user creating a new table

Question

How to stop a user creating a new table

Rupster

SSC Enthusiast

Points: 174
More actions
March 18, 2021 at 4:25 pm

#3859559

I'm experiencing a problem where tables in my SQL Server database are getting randomly re-created and all the old records are being lost.
I would therefore like to prevent a particular user from being able to create any new tables in the database.
Is there a role I could assign to the user to achieve this, or would I need to create a new role? If so, how?
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ratbak Ten Centuries Points: 1379 More actions · Answer 1

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15

Only db_ddl_admin & db_owner roles inherently have create table permissions.

db_datareader, db_denydatareader, db_datawriter, & db_denydatawriter roles do not.

Rupster SSC Enthusiast Points: 174 More actions · Answer 2

Thanks.

I want the user to be able to insert, amend and delete records within tables, but not to be allowed to create or delete any tables.

How do I achieve this?

Jeff Moden SSC Guru Points: 1004378 More actions · Answer 3

Rupster wrote:

Thanks.
I want the user to be able to insert, amend and delete records within tables, but not to be allowed to create or delete any tables.
How do I achieve this?

@ratbak just told you how. What privs does the user in question currently enjoy? Also, what groups is the user a member of that might also provide such privs?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 101108 More actions · Answer 4

Grant the user the db_datareader and db_datawriter roles, etc., using the commands below. You can ignore any errors except those dealing with the user's name:

ALTER ROLE db_datareader ADD MEMBER [your_user_name_here];

ALTER ROLE db_datawriter ADD MEMBER [your_user_name_here];

ALTER ROLE db_ddladmin DROP MEMBER [your_user_name_here];

ALTER ROLE db_owner DROP MEMBER [your_user_name_here];

IF you want them to be able to run already created procs in the db, then run this too:

GRANT EXECUTE ON SCHEMA::dbo TO [your_user_name_here];

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ScottPletcher SSC Guru Points: 101108 More actions · Answer 5

If necessary, you could even add a DDL trigger as a failsafe to make absolutely sure that user doesn't create (or drop) any tables in that db.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Rupster SSC Enthusiast Points: 174 More actions · Answer 6

The user has no owned schemas, and their only membership is the db_owner role.

OK, so in SSMS I'll remove their db_owner membership, and tick the db_datareader and db_datawriter roles.

(And I can use a DDL trigger to monitor any breaches.)

Thanks.