groups in sql

Question

Post reply

groups in sql

srimkumarp

Old Hand

Points: 359
More actions
February 25, 2008 at 3:20 am

#127993

hi all ,
i've one doubt on groups in sql server .
i want to create one group in sql server and for that group i have to assign only read,write and execute permissions to run application for end users .
is it possible to manage like this?
can any one help on this issue?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Preethiviraj Kulasingham Mr or Mrs. 500 Points: 583 More actions · Answer 1

You can minimize the administrative work by using Roles.

Create a Role and Give all the permissions to that role. Add the users to that role.

Either you can give Execute, Insert, Update , Delete and Select Permissions to the schema level or object level (each object) to control this. Otherwise, grant the execute permission to the role add db_datareader and db_datewriter roles to the user.

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!

srimkumarp Old Hand Points: 359 More actions · Answer 2

thanks for reply

we have so many databases and also thousand of logins.

creating roles in each and every database and assign logins to that db is some what difficultly ,

previously i tryed and failed.

i joined new to the company perviously no one was maintaining server.

i am the first person.

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

It takes time and effort, but once you've done it, you're done. So it's a one time task and you just have to do it. There's no shortcut since it's not easy to determine what access you need.

You should use roles and then it might be possible to script adding people to the roles, depending on what your requirements are.

srimkumarp Old Hand Points: 359 More actions · Answer 4

srimkumarp

Old Hand

Points: 359

February 25, 2008 at 10:48 pm

#781855

ok thanks.

that i'll do it..

Preethiviraj Kulasingham Mr or Mrs. 500 Points: 583 More actions · Answer 5

If you want the same rights over all databases, you can also use sp_msForEachDB stored procedure. But if your need is different from database to database, you need to do it manually.

Cheers,
Prithiviraj Kulasingham

Plan to Test your Plan!

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

I'd add one more level of abstraction to this: create one (or more) security groups, populate them with the users and add the groups to your roles. That way, you manage the question of WHO has permissions WHERE mostly at the Windows level, not the SQL level. Permissions of users being hired/fired in your organization will be transparently managed by your sysadmins, not the DBAs.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 7

Yes groups and roles will do the trick. With 2005 you can even assign granular permissions to the objects. So assign only those permissions to the users and not that are not required.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com