Permissions/role for DTS user

[BFarley]

We have a user who only uses the SQL Server 2000 database to run one or more DTS packages that reads from tables & exports the data. The user should be able to do nothing else.

In particular, we want to restrict the user from seeing - let alone modifying - any stored procedures, user-defined functions, etc.

Which would make more sense: setting the user's membership to one of the predefined roles (db_datareader?), or creating a specialized role that defines SELECT permissions on each table?

Thanks much in advance.

Bryant Farley

"The Dude Abides

 

[ousoonerjoe]

If you are truly wanting to lock this person down, I would create a new group that has specific items allowed. Add this person to that group. Creating a group allows you to add other "viewers" with much more ease when the need arises (and it always does).

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[BFarley]

That makes sense to do it that way - agreed that the "just this one time" need seldom stays that way

Is there a way to prevent a user from viewing stored procedure, user defined function, etc code from within SQL Enterprise Mgr based on group/role membership?

Bryant Farley

"The Dude Abides

 

[ousoonerjoe]

Under the permissions, you can select each individual object and define exactly which ones can be executed (sp), SELECT, DELETE, INSERT, UPDATE (table, view), etc. I'd start there.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.