MS SQL Server security - How do disable a user to "Generate SQL Scripts"

Author

How do disable a user to "Generate SQL Scripts"

Joao Rego

2005-09-29, 7:23 am

I have an SQLServer 2000 instance with a DataBase.
I have 2 users: sa, new_user.
The [new_user] has membership role: public, db_datareader, db_datawriter
It has permissions to manipulate some table and stored procedures. He can
not export, import, backup, restore the data base.

The point is that he can generate a script for the all database!!!
How do I disable it?
Can some one give me some help on this issue.

Thanks,
JoaoRego

Matt Neerincx [MSFT]

2005-09-30, 3:23 am

Since you give the user db_datareader membership, then they can read
anything in the database, including the structure of the tables.

You can deny permissions to system tables like sysobjects to prevent listing
tables for example, like so ->

deny select on sysobjects to user1

This would prevent user1 from getting a list of tables for example.

Another common scheme is to only allow user's access to views and totally
restrict access to the actual underlying tables.

--
Matt Neerincx [MSFT]

This posting is provided "AS IS", with no warranties, and confers no rights.

Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Joao Rego" <Joao Rego@discussions.microsoft.com> wrote in message
news:4B2E9F96-E012-4CF3-8033- A19A6E0DD797@microso
ft.com...
>I have an SQLServer 2000 instance with a DataBase.
> I have 2 users: sa, new_user.
> The [new_user] has membership role: public, db_datareader, db_datawriter
> It has permissions to manipulate some table and stored procedures. He can
> not export, import, backup, restore the data base.
>
> The point is that he can generate a script for the all database!!!
> How do I disable it?
> Can some one give me some help on this issue.
>
> Thanks,
> JoaoRego

Sponsored Links