Minimum rights required.

Question

Post reply

Minimum rights required.

Joy Smith San

SSC-Insane

Points: 24877
More actions
April 12, 2010 at 11:44 pm

#142542

Dear All,
I want a particular login to have the following rights.
1. Create/Alter Tables.
2. Read/Write into All the tables.
3. Create/Alter Procedures, functions, Triggers etc..
4. Execute all the Procedures, functions, Triggers etc.
Whats rights I need to give for that user, Pls advice.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve-3_5_7_9 SSChampion Points: 10832 More actions · Answer 1

Into "all" tables. Does this include the system tables?

I would do this with two logins.

One login would have DDLAdmin, datareader, datawriter and this login would exist only in DEV.

For the 2nd login, I would create a role and assign the appropriate permissions. This login would be used by the application or users.

Select on user tables

Execute on sprocs and functions

I would control the access to all of the user tables via the sprocs. I probably would NOT grant "datawriter" to this role because permissions should always be granted minimally and the user/application probably doesn't need to "write" to all tables.

Now if you're just looking for the easy way, the answer is:

DDLAdmin

Datareader

datawriter

Depending on the schema the sproc is created with, you'll probably need to grant "exec" on the sprocs and functions

DBOwner will take care of the execute as well but again I wouldn't recommend this.

dbychen SSCommitted Points: 1889 More actions · Answer 2

You can implement these with SQL codes in SQL Server 2005 and the newer versions. T-SQL codes are better than the traditional server/database roles, because user needs can be granted more precisely. Here is a short list that should be applicable:

GRANT CREATE TABLE TO Mary, [DOMAIN\JSimith];

GRANT SELECT, INSERT, UPDATE, DELETE ON <Table_Name> TO Mary, [DOMAIN\JSimith];

GRANT CREATE PROCEDURE, CREATE FUNCTION TO Mary, [DOMAIN\JSimith];

GRANT ALTER PROCEDURE, ALTER FUNCTION TO Mary, [DOMAIN\JSimith];

GRANT EXECUTE ON usp_<NAME> TO Mary, [DOMAIN\JSimith];

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

Don't grant rights to users. Use roles. You can create your own with these rights, using the commands above, and then assign users to roles. I'd do two roles. One for read/write/execute and one for changing objects.