Do NOT grant permission to create or drop any objects in teh database

Question

Post reply

Do NOT grant permission to create or drop any objects in teh database

Guras

SSCertifiable

Points: 6518
More actions
August 26, 2011 at 2:27 pm

#390288

I have a AD usergroup mycompany\AppUsers . The users within this group will be running on SELECT query on the database mydatabase.
Now I would like to prevent the users from creating any objects in the database or dropping . Can you please explain how can I go about doing that?
Thank you in advance.

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

fiarly straight forward, since by default, they have no rights to do anything that you don't give them rights to.

here's a very simple example, but esentially you want to do the following:

1. create a role with the right permissions,

2. add the Add group as a user to the database

3. add the AD group to the role.

USE [SandBox] --my db for users to do stuff.

CREATE ROLE [ReallyReadOnly]

--give my new role READ permission to ALL tables

EXEC sp_addrolemember N'db_datareader', N'ReallyReadOnly'

--explicitly DENY access to writing

EXEC sp_addrolemember N'DB_DenyDataWriter', N'ReallyReadOnly'

--give my new role permission to run the procedures you've created? uncomment if true

--GRANT EXECUTE TO [ReallyReadOnly]

--now since we know the AD domain group as a LOGIN exists, lets add a USER, tied to that login, to our database

--make a user in the db for the matching login

CREATE USER [MyDomain\ThatBizGroup] FOR LOGIN [MyDomain\ThatBizGroup]

--finally, add our user bob to the role we created

EXEC sp_addrolemember N'ReallyReadOnly', N'MyDomain\ThatBizGroup'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Guras SSCertifiable Points: 6518 More actions · Answer 2

But does 'DB_DenyDataWriter' prevent them from being able to create and drop objects in that database? In other words if I can give the role read and insert and update in some table but they cannot create any tables or stored procs, view etc in that database. HOw do I do that?

Thanks for your help!

Lowell SSC Guru Points: 323518 More actions · Answer 3

Guras (8/26/2011)
But does 'DB_DenyDataWriter' prevent them from being able to create and drop objects in that database? In other words if I can give the role read and insert and update in some table but they cannot create any tables or stored procs, view etc in that database. HOw do I do that?
Thanks for your help!

the roles db_owner or db_ddladmin are the ones that give the ability to create or destroy objects like tables, view, procs etc.

if you do not give them those rights they cannot do it;

does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Guras SSCertifiable Points: 6518 More actions · Answer 4

Lowell (8/26/2011)
Guras (8/26/2011)
But does 'DB_DenyDataWriter' prevent them from being able to create and drop objects in that database? In other words if I can give the role read and insert and update in some table but they cannot create any tables or stored procs, view etc in that database. HOw do I do that?
Thanks for your help!
the roles db_owner or db_ddladmin are the ones that give the ability to create or destroy objects like tables, view, procs etc.
if you do not give them those rights they cannot do it;
does that help?

Ah! I get it now. Thanks a bunch!