Hi All,
I administer a production database. I wanted the user to read the
database using a query analyser or anyother application.

But I should avoid any user to use the DTS Import/Export data wizard
to take out the data. Since the data being very critical to the
business, I don't want any one to replicate the data in any format.
viz: text file,excel sheet or moving to any other data source.

Thanks,
Ganesh

You are going to have a hard time then.

Forget DTS. What about BCP?, Access, Excel or any other tool such as this? They have the ability to extract so long as you have
SELECT permissions and this is the permission you want to give the user to be able to read the data.

The only way I can see you getting around this would be to create your own tool to query the DB but implement it using Application
Roles. This means that when the user is not using your tool then they have no permissions and if they are using your tool then you
control what they do.




--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"Ganesh Babu" <ganesh.kaliaperumal (AT) wipro (DOT) com> wrote

For BCP, in order to execute the xp_cmdshell the user has to be the
database owner. But I do agree with access and excel there is a pitfall.

I appreciate your idea too. This kind of permission I will have to give
it to the production support. By that way, I can ensure the data
wouldn't be transferred by any way or any means.

Let me wait for someone's input and look forward positively.

Have a nice time

Cheers,
Ganesh






*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Who said anything about using BCP from within SQL Server?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.Konesans.com


"GaneshBabu kaliaperumal" <ganesh.kaliaperumal (AT) wipro (DOT) com> wrote

I would also be interested to know where you read about having to be the
database owner to execute xp_cmdshell.

From BOL.

When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in which
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail. This is true only for
Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no
impersonation and xp_cmdshell is always executed under the security context
of the Windows 9.x user who started SQL Server.

The permission for xp_cmdshell defaults to the sysadmin role but this can be
given to non-sysadmins also.


Allan

"GaneshBabu kaliaperumal" wrote: