Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

automatically grant all DML permission on all MS SQL schema

Posted on 2016-09-23
28
73 Views
Last Modified: 2016-09-28
hi,

we are now undergoing the permission review and we tried to make less database role, so I combine all permission from previous role to a single role so all developer under that role will all have the same permission now.

one thing, is that true that all newly created schema will not automatically grant full DML permission for a role ? must be manually when each time a new schema created?

or as long as the database role also the owner of  that user database, then ANY NEW CREATED schema will be able to fully DML able ?
0
Comment
Question by:marrowyung
  • 15
  • 12
28 Comments
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41812181
I'm not sure if I understood your question but when you create a new schema you'll need to provide later what roles or users will have permissions on it. There's no way for SQL Server engine guess what's going on your mind.
Take for example a Stored Procedure. After you created you'll also need to provide the necessary permissions to a user or role can execute it. With a schema or any other database object is mostly the same.
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 250 total points
ID: 41812193
Your post raises some more questions about development processes and strategies:

1) How many developers?
2) Why not using dedicated databases for each developer?
This allows distributed development. And when pulling the changes to the main trunk, then permissions are assigned on the shared master.
3) Why must developers create schemas?
Schema creation is not part of the normal development process. Schemas are part of the architecture, thus a part of the "interface".
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41815472
Victor,

"I'm not sure if I understood your question but when you create a new schema you'll need to provide later what roles or users will have permissions on it. There's no way for SQL Server engine guess what's going on your mind."

right now the problem is the permission is diff from developer to developer, and we are combine their right all together to a single role.

so I am thinking why I don't have all these problem but they have, the DB roles, man. and we have a new requirement to have only ONE NEW DB role, consolidate all permission in to this role for easier management.

but I am thinking about if the user is the db_owner of that user database, he/she can simply just can have all NEW schema created without missing permission on extra schema DML operation .

I don't want some developer keep complaining about they don't have permission on a schema.

ste5an,

"1) How many developers?"

We are planning this for all developer in US and in asia, around 30.

" Schemas are part of the architecture, thus a part of the "interface"."

good point, they create that as part of the development and it make sense as they design the architecture.

is it if about interface, interface team should handle that?

we don't use this approach in here but please explain why approach is good?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41815481
To help you with a better solution I would need to know how is your development team structured but for now my main question is:
Why a developer can't have db_owner roler in a development database?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41815489
"Why a developer can't have db_owner role in a development database?"

we want to remove as much permission as possible and just enough for them to work.

if the consolidation is working well we may copy the whole thing to production and then this is not good to let developer be the db_owner of the production man.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41815496
we want to remove as much permission as possible and just enough for them to work.
And what are the enough permissions for them to work?
Will they need to create user, roles and schemas?
Will they need to create tables, views, stored procedures, functions, sequences, triggers, indexes, ...?
Will they need to backup and restore the database?
Will they need to grant and revoke permissions from other users?

this is not good to let developer be the db_owner of the production man.
Of course not. But are we talking about development databases or production databases?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41815499
here the team will do something stupid , i.e. make the DB down by doing select * from a table which is under merge replication, then DB can't response anymore.

we don't want to let developer do too much ACTION.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41815513
"And what are the enough permissions for them to work?"

at this moment, what they request is just insert/update/delete/alert/execute.

so no need db_owner actually but for each schema, we might need to involved.

"Will they need to create user, roles and schemas?"

just schema, development need. for any new project.

for us. as long as they do what they need to do , then no need db_owner role. but for newly created schema, I doubt.

the one is the db_owner, never has problem but the one who is not !

"Will they need to create tables, views, stored procedures, functions, sequences, triggers, indexes, ...?"

yes.

development related.

"Will they need to backup and restore the database?

no. good point, our job.

"Will they need to grant and revoke permissions from other users?"

no. that;s why we need to think about that.

"Of course not. But are we talking about development databases or production databases?"

image we use the same role and permission assignment method ACROSS production and TEST DB/
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41815530
Then I think this 3 database roles should answer your requirements:
  • db_ddladmin
  • db_datawriter
  • db_datareader

image we use the same role and permission assignment method ACROSS production and TEST DB/
What's matter is if the developer logins are also created in PROD. If not then you won't have any problem because they won't be able to connect to PROD anyway.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41815766
"db_ddladmin
db_datawriter
db_datareader
"

yes, but we are more on doing it for tempdb connection as we create a lot of tempdb object.

we are also doing this but really don't have to give more like db_owner.

"What's matter is if the developer logins are also created in PROD"

we don't want to use db_owner, too much right.

so we can assume that we only create the same role with these permission only:

"db_ddladmin
db_datawriter
db_datareader"

ACROSS both production and TEST.

agree ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41815771
yes, but we are more on doing it for tempdb connection as we create a lot of tempdb object.
I wouldn't concern with tempdb at all.

agree ?
Yes, having the same roles in all environment is good but I'm talking about Logins. You can't have the same logins in all environment. Production environment should be more restricted (only DBAs and application login).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41817146
"I wouldn't concern with tempdb at all.
"

sorry, we need to as here we need to create a lot of tempdb object and from time to time they will complain they can't run any SP as the SP code generate a lot of #<table>

so we here also grant the 3 x permission for each need to run that on tempdb as well.

"Production environment should be more restricted (only DBAs and application login)."

agree !

but here, developer will need to troubleshot it by doing the same thing on production, sometimes hard to deny them.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41817185
but for permission for new schema, the 3 x permission still not enough ? still not need dbo_owner.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41817265
What I mean is to not care at all with permissions on tempdb. I think we are talked about this a couple of months ago. Going for this kind of solution is what is putting you in this mess.

but here, developer will need to troubleshot it by doing the same thing on production, sometimes hard to deny them.
I agree but that's why you have some good options as giving them a backup with masked data from Production so they can test it as will.

but for permission for new schema, the 3 x permission still not enough
Why not? What they still can't do with those permissions?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41817319
"I agree but that's why you have some good options as giving them a backup with masked data from Production so they can test it as will.
"
you mean restore production DB to TEST DB And then masked sensitive data on it before giving them?

we are already doing this but when they need to real troubleshoot on why TEST DB and production DB not behavior the same sometimes, it exists in all company I worked with.

"Why not? What they still can't do with those permissions?"

back to reply you told me above, SQL server is not going to know what permission we need and we have to assign it ourselves.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41817326
we are already doing this but when they need to real troubleshoot on why TEST DB and production DB not behavior the same sometimes, it exists in all company I worked with.
What's the difference?
Anyway you as a DBA you should have the enough knowledge to troubleshooting also.

back to reply you told me above, SQL server is not going to know what permission we need and we have to assign it ourselves.
Sure. That's why I provided those 3 database roles. With that they should be able to perform everything without being db_owners.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41817354
"Anyway you as a DBA you should have the enough knowledge to troubleshooting also."

no, my question here is should I make them the db_owner of the DB before they automatically grant DML right on ALL NEWLY CREATED SCHEMA.

"With that they should be able to perform everything without being db_owners."

ok, even for newly created schema.. you mean, that right?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41817355
ok, even for newly created schema.. you mean, that right?
Yes. You don't need to give them db_owner. A db_owner has access to all schemas.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41817359
"A db_owner has access to all schemas."

all schema, this is what they want. but still no need, right?
0
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41817385
Sorry, I've thought that you already decided to not go for schemas.
If so, then forget about the above and you'll need to create a database trigger to grant the necessary permissions for each created schema:
CREATE TRIGGER trg_DBSchema ON DATABASE
    FOR CREATE_SCHEMA
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA()
	DECLARE @SchemaName sysname
	DECLARE @GrantCommand VARCHAR(MAX)

	SET @SchemaName = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'sysname')
 
 	SET @GrantCommand = 'GRANT ALTER ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

	SET @GrantCommand = 'GRANT DELETE ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

	SET @GrantCommand = 'GRANT EXECUTE ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

	SET @GrantCommand = 'GRANT INSERT ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

	SET @GrantCommand = 'GRANT SELECT ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

	SET @GrantCommand = 'GRANT UPDATE ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

	SET @GrantCommand = 'GRANT VIEW DEFINITION ON SCHEMA::' + @SchemaName + ' TO [UserName]'
	EXEC(@GrantCommand)

END

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819260
"If so, then forget about the above and you'll need to create a database trigger to grant the necessary permissions for each created schema:"

sorry this one is a on database level and is a DDL trigger?

as long as the reader, writer and ddl_admin it not enough for any NEW schema, then I need this kind of tools.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819266
On database level. You can state that by the CREATE TRIGGER name ON DATABASE keyword ;)
Have that trigger created in each database and you won't need to worry more about permissions on schemas.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819311
yeah, looks perfect. i see it now :):)

that;s why I HATE coding.

so in this way I have to hard code [UserName] ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819326
You can hardcode username or modify the script to receive username as a parameter.
If developers are included in a Role or in an Active Directory group then replace the UserName with the respective Role name or AD group name.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41819476
"If developers are included in a Role or in an Active Directory group then replace the UserName with the respective Role name or AD group name."

ok this time also work for database role name ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 41819482
Yes. You can GRANT or DENY permissions to Principals and a Principal can be:
Windows-level principals
•Windows Domain Login
•Windows Local Login

SQL Server-level principals
•SQL Server Login
•Server Role

Database-level principals
•Database User
•Database Role
•Application Role
0
 
LVL 1

Author Comment

by:marrowyung
ID: 41821091
excellent. I will make use of it
0
 
LVL 1

Author Closing Comment

by:marrowyung
ID: 41821095
tks victor.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question