Permissions Question

Question

Permissions Question

Atat Rowcount

Default port

Points: 1453
More actions
September 24, 2010 at 3:38 am

#387857

Hi
I need help with setting up permissions in SQL Server. There are just two different levels of permission required:
(a) The ability to *just* execute existing stored procedures, and to create (but not drop/modify/etc) databases
(b) The ability to *just* execute existing stored procedures, and to run select (but not insert/update/etc) commands
I need the ability to assign either (a) or (b) permissions when adding new users.
I need the ability when creating new databases, to automatically have the permissions for that database matching those of others, for whichever users exist.
Any help would be greatly appreciated. I have read the documentation around permissions in SQL Server, however anything I can find assumes an existing level of knowledge, which I......kind of have......but I don't know enough about it to properly understand the documentation I am reading!

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

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

crazy4sql SSCoach Points: 19590 More actions · Answer 1

(a) The ability to *just* execute existing stored procedures, and to create (but not drop/modify/etc) databases

creat a group and give 'Grant exec' to that perticular group. Later on you can add/remove users in that group. dbcreator is server level permission for only creating the database.

(b) The ability to *just* execute existing stored procedures, and to run select (but not insert/update/etc) commands

same as above and you can give that group as datareader permission on specific database/s to run select commands.

----------
Ashish

Atat Rowcount Default port Points: 1453 More actions · Answer 2

Hi

Sorry, pardon my ignorance, but what's a group? How do I create a group?

Brian

crazy4sql SSCoach Points: 19590 More actions · Answer 3

you need to create the group in Active Directory. Then that group will be given required permission in sql

----------
Ashish

UMG Developer SSChampion Points: 13482 More actions · Answer 4

learningforearning (9/24/2010)
(a) The ability to *just* execute existing stored procedures, and to create (but not drop/modify/etc) databases
creat a group and give 'Grant exec' to that perticular group. Later on you can add/remove users in that group. dbcreator is server level permission for only creating the database.

dbcreator is not only for creating databases, it also allows them to DROP/ALTER any existing database. The way to meet the requirements is to use this:

GRANT CREATE ANY DATABASE to (Developer/Group)

Of course that still allows them to DROP/ALTER the database(s) that they created. I don't think there is any way to allow a user to create a database and then not be able to DROP/ALTER it.

UMG Developer SSChampion Points: 13482 More actions · Answer 5

Brian McGee-355525 (9/24/2010)
I need help with setting up permissions in SQL Server. There are just two different levels of permission required:
(a) The ability to *just* execute existing stored procedures, and to create (but not drop/modify/etc) databases
(b) The ability to *just* execute existing stored procedures, and to run select (but not insert/update/etc) commands

For the SELECT permissions you would grant the db_datareader permission.

I need the ability to assign either (a) or (b) permissions when adding new users.
I need the ability when creating new databases, to automatically have the permissions for that database matching those of others, for whichever users exist.

I think to do this you would have to setup your permissions in the model database, since it is the base for all new databases.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

to create a grant script for all user stored procedures use the following

select 'grant execute on object::[' + sch.name + '].[' + pr.name + '] to userorrole'

from sys.objects pr inner join sys.schemas sch on pr.schema_id = sch.schema_id

where pr.is_ms_shipped <> 1 and pr.type = 'P'

UMG Developer (9/25/2010)
GRANT CREATE ANY DATABASE to [Developer\Group]

😉

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉