System tables in SQL server

[Parasu]

I create a new database and a new user for it and give him db_datareader, db_datawriter and db_ddladmin roles. Other than this, Im denying permission on select, insert, update, execute and delete on all the system tables in his particular database. Will this denying of access to the system tables cause any problem while his is trying to insert or update or do anyother DDL activity from the database.
Parasu

 

[Tim1]

Hiya Parasu,

You will give him major problems if he needs to check if a table exists in a script before dropping/creating a table. In order to accomplish this task, he will need select access to sysobjects, and will need select access to sysindexes if he creates indexes in the database.

Additionally, I think that if he does not have full privilages to those system tables, he may find problems with creating and dropping indexes and tables, because of the changes it makes to those tables. That will also then follow onto syscolumns. You may find it easiest to give him full access to system tables unless there is a pressing reason not to.

Tim

 

[Parasu]

Thanx for that Tim,
There is no pressing issue on not allowing the user to access the system tables. The only reason I thought was that, he might mis-handle the system tables which might lead to a problem with the database itself.

Cheers and thanx again
Parasu

 

[Parasu]

One more thing Tim,
If I restrict access to the system tables in Master and tempdb database and I give rights to the clients database's system tables, will it affect the creation and other things?

Cheers Parasuraman

 

[Tim1]

Hiya Parasu,

I am not quite sure what will happen if you restrict access on tempdb, I generally tend to give users access to these tables, but although we allow users select access to master, they don't get any write/update privilages to anything in there, as there is nothing for users to do in there unless they create their own dbs.

HTH,

Tim

 

[Parasu]

Thanx for that Tim
Parasuraman