Security inheritance

[sparkbyte]

I would like to setup the security on a database so that each user of an AD security group can...

see all the tables owned by dbo

alter, create, delete tables in the DB

instert, update, delete data from the tables

create, alter, execute, update
Stored Procedured
Functions

I am pretty sure it has to be done using a combination of Server roles, DB Roles, and schemas. I just can't seem to find a good explaination of how to accomplish it.

Once I learn how do do this, I will start to restrict priv's further based on job restrictions.



Thanks

John Fuhrman

 

[gmmastros]

It sounds to me like you are describing db_owner. If a login is a member of db_owner, that user will be able to do anything within the database, but won't necessarily have privileges to the server.

I recommend you take a look at [!]sp_addrolemember[/!]. While you are looking, also take a look at [!]sp_addsrvrolemember[/!]. This last one would be used to add members to sysadmin, backup operator, etc....



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Through a lot of trial and error. I think I have worked most of it out.

As you say granting access through dbo (db_owner) can do anything withing any given DB a user has access to.

With that said. db_owner and several other schema's are created when a database is created. dbo is generaly the default user and schema of that database, hence making a user/group a member of db_owner grants that user a fair amount of rights within that database.

Now, I have created a new schema called db_Mailroom and made the owner of the schema db_Mailroom.

Make my test user only a member of db_Mailroom. At this point the test user cannot see any tables, views, stored procedues, or functions. (they are all owned by dbo)

CREATE TABLE 
	  dbo.tblTrackingParseTrial(Tracking_ID int
	, EmployeeID varchar(50)
	, MachineName varchar(20)
	, BoxNumber varchar(45)
	, FileNumber varchar(25)
	, TrackingDate Datetime
	, TrackingNumberPrefix varchar(2)
	, TrackingNumberAct varchar(6)
	, TrackingNumberShipping varchar(2)
	, TrackingNumberParsel varchar(8))

Test user cannot see the new table. Table was created in schema dbo.

CREATE TABLE 
	  db_Mailroom.tblTrackingParseTrial(Tracking_ID int
	, EmployeeID varchar(50)
	, MachineName varchar(20)
	, BoxNumber varchar(45)
	, FileNumber varchar(25)
	, TrackingDate Datetime
	, TrackingNumberPrefix varchar(2)
	, TrackingNumberAct varchar(6)
	, TrackingNumberShipping varchar(2)
	, TrackingNumberParsel varchar(8))

Test user can now see only the new table that was created with the schema db_Mailroom.

So, if I am following this correctly, I hope, if a users default schema is dbo they should be able to do anything within a DB they have access to. If their default schema is one that I create, their access will depend on the explicit rights given to the schema.


I hope I am somewhere close. Any other insights would be helpful. Links to good SQL 2005 security tutorials.



Thanks

John Fuhrman