Disable Generate Scripts on Database

Hi guys, is it possible to disable database generate scripts for certain users? I would like to restrict it for security purposes while they able to get into the db and their role like the db owner. Hope can any assistance from you guys. Thanks a lot.

Best Regards,

Hans

You can't do it directly; but there is a workaround there.

1. Add the user in Master database

2. Select the db_denydatareader & db_denydatawriter role

3. Deny execute permission of the sp_helptext & etc.. for the given user

|||Thx for the reply Manivannan.D.Sekaran, however it seems not working at all.|||

If you don't want your users to have access to the definitions of your Stored Procs, Views, Triggers etc... then you could always re-create the procs with the WITH ENCRYPTION option - just ensure that you store copies of the original scripts in a source control database.

Chris

|||

Hans1982 wrote:

Thx for the reply Manivannan.D.Sekaran, however it seems not working at all. I wish to create a login for developer that able modify things within the db but cant backup/restore db, generate the whole db scripts and unable to execute 'Script table as' feature.

|||Thx for the reply,Chris. However, I just wish to the developer unable to generate the db scripts or table scripts for preventing the db structure bring out from the office.|||

I can't think of away of doing this other than taking SSMS away from the developers, which would obviously be counter-productive. I guess you could always write your own version of SSMS if this is a big issue for you.

The problem is that the 'Script table as' option directly queries system tables and views that are essential to the running of SQL Server and SSMS. Although you can deny permissions on these objects, doing so is likely to have wide implications.

Why do you need to restrict access to the 'Script table as' functionality?

Chris

|||For preventing the db structure bring out from the office.|||

Any developer who has enough knowledge of SQL Server's system tables and views would be able to generate their own scripts to reconstruct the structure without using 'Script As'.

Perhaps you need to look at ways of preventing people from copying / emailing script files from work PCs. Even then there's nothing to stop someone printing out a database diagram and taking it home, or even resorting to copying the structure using more traditional techniques, such as pen and paper.

If your database schema is of such high value then maybe you should address (at least) the above issues and add confidentiality clauses into your developers' contracts. Either that or simply don't allow the people you don't trust to work on your databases.

Chris

|||

I see. I guess that's the only solution for that issue because it seems no way to disable the SMO. Thanks for the good advice, Chris. Have a nice day.

Best Regards,

Hans