April 13, 2011 at 5:53 am
Hi
How to deny permission to user for generating databse script in sql sever 2008 or 2005.
I don't want to allow the user to generate script for a database like this
1.Right click on the database
2.Select TASKS
3.Select GENERATE SCRIPTS
Is their any way to do this
User is a member of db_datareader
thank you in advance
With regards
DD
May 16, 2012 at 6:39 am
Is it possible to do that
May 16, 2012 at 6:50 am
I have just tested this against my test user limiting permissions to db_datareader only and they cannot export the scripts. They can get all the way through the wizard but it fails with the below exception when running
Microsoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
May 17, 2012 at 12:39 am
Also you can deny view definition to the user
Deny View Definition To [User]
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
May 17, 2012 at 4:40 am
anthony.green (5/16/2012)
I have just tested this against my test user limiting permissions to db_datareader only and they cannot export the scripts. They can get all the way through the wizard but it fails with the below exception when runningMicrosoft.SqlServer.Management.Smo.SmoException: Could not read metadata, possibly due to insufficient access rights. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
I tried this but i am able to generate the script...did u set anything else
May 17, 2012 at 4:41 am
Sachin Nandanwar (5/17/2012)
Also you can deny view definition to the userDeny View Definition To [User]
If i do this developer cannot see the procedures....all the procedures will be locked out
May 17, 2012 at 4:43 am
if they have view definition then they will be able to script out the objects as you have explicitly granted them the access they need to view the definition of the objects
May 17, 2012 at 5:06 am
thanks for the reply...yes it is true when i deny the VIEW DEFINITION to user,he cannot generate the script.
But i cannot deny the user the VIEW DEFINITION...is there any workaround for this to stop the user.
If i deny the user the view definition then all the procedures get locked out
May 17, 2012 at 5:10 am
well there is no difference between granting view definition and scripting, if they have 1 they can do the other, so the queston is, do you want to limit developers so that they cannot see the definitions of the objects? if no then they can script, if yes then they cant script or view the objects
May 17, 2012 at 5:29 am
so it is not possible without denying the view definition..
In my company the application developers want to see the procedures...so it is not possible to deny the VIEW Definition.
The problem comes when they generate the script and take it to home...most of the times when they leaving the company they take all the script DB.
Microsoft should define seperate permission for this
May 17, 2012 at 5:35 am
do you not enforce IP rights, where anything company related is the companies and if used elsewere is in breach of these rights and can lead to the developer being taken to court for infringing their terms and conditions of employment etc.
May 17, 2012 at 5:38 am
you could script the procedures out and put them in a locked down share on the network where only read permissions are granted, could render the file in a pdf and set the security to not allow content copying, or copying of the file.
if its only procs they need to see then create a role and just grant view definition to the procs instead of the whole database that way they only see the procs, they cant script out the tables.
May 17, 2012 at 7:10 am
anthony.green (5/17/2012)
if its only procs they need to see then create a role and just grant view definition to the procs instead of the whole database that way they only see the procs, they cant script out the tables.
Anthony thank you very much it worked
One more question is it not possible at server level..because if there is 50 database then i have to create role in all 50 DB and assign user to this role in each db right? or i am missing something
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply