March 18, 2021 at 4:25 pm
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.
March 18, 2021 at 4:59 pm
Only db_ddl_admin & db_owner roles inherently have create table permissions.
db_datareader, db_denydatareader, db_datawriter, & db_denydatawriter roles do not.
March 18, 2021 at 5:16 pm
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?
March 18, 2021 at 6:09 pm
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
Change is inevitable... Change for the better is not.
March 18, 2021 at 7:01 pm
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".
March 18, 2021 at 7:05 pm
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".
March 19, 2021 at 4:20 pm
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.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy