Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Minimum rights required. Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 11:44 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:14 PM
Points: 1,538, Visits: 2,586
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.
Post #902137
Posted Wednesday, April 14, 2010 5:56 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 26, 2014 6:02 AM
Points: 919, Visits: 1,434
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.










Post #903031
Posted Wednesday, April 14, 2010 8:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 28, 2014 11:24 AM
Points: 137, Visits: 498
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];




Post #903217
Posted Wednesday, April 14, 2010 9:06 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:17 AM
Points: 31,080, Visits: 15,526
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #903238
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse