Securing objects for a new user (what's the easiest way?)

Question

Securing objects for a new user (what's the easiest way?)

FishD

Right there with Babe

Points: 784
More actions
October 13, 2014 at 7:10 am

#150786

Hi
Added a new login and user for dbo.myDatabase . Its got a lot of user generated SP's, tables and views.
I want the SP's to be executble, the tables and views to be insert,delete,alter.
I tried with the SSMS console, but when i get to, for example the tables, I have to select the table from the Securables window, and then click the boxs for the explicit permissions for the object.
This becomes very tedious (and error prone), for all the objects. (would be nice if i could 'multiselect' from the box above, and then select the permissions (like i do in msaccess!)
Is there an easier way ?
Cheers.
Gerry

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

The GUI for object security is awful. Don't use the UI, just run a script.

However, if you want to grant execute on all procedures and insert/update/delete on all tables, you can do it directly at the database level. Select in the securables panel all objects of the type "database" and check execute, insert, update and delete.

With a script it's much easier:

GRANT EXECUTE TO myUser;

GRANT INSERT TO myUser;

GRANT UPDATE TO myUser;

GRANT DELETE TO myUser;

-- Gianluca Sartori

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

You can also grant multiple permissions on a single instruction.

GRANT INSERT, SELECT, UPDATE, DELETE ON ObjectName TO UserName

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

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

Noooooooooooooooooooooooooooooooooooo (screaming, shakes head, angry face).

We do not grant rights for users. Only roles. Use roles. Please. Roles.

Pretty please.

And the Gui is a mess. Use it if you must, but script the permissions out and save them. In a VCS.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Steve Jones - SSC Editor (10/15/2014)
Noooooooooooooooooooooooooooooooooooo (screaming, shakes head, angry face).
We do not grant rights for users. Only roles. Use roles. Please. Roles.
Pretty please.
And the Gui is a mess. Use it if you must, but script the permissions out and save them. In a VCS.

😀 LOL!

Yes, agreed. Roles are the way to go and what I actually do in production.

-- Gianluca Sartori

FishD Right there with Babe Points: 784 More actions · Answer 5

Cheers guys,

I have used the script approach before, when adding new SP's for example, but its still tedious when i gotta list out all my stored procedures for example and iterate through them.

In a script i'm guessing the process would look something like ?

for each stored procedure in my list

capture the SP name

Assign permissions for object capture name to my user

next

When i get my head around this part, i'll look at creating a new role, with those priviliges, and assigning the user that role. That makes sense.

EDIT

Created a role ....webClientRole,

used the GRANT EXECUTE ON SCHEMA::[dbo] TO webClientRole (not so granular but did the job.)

Deleted my existing user, and recreated in the DB, and just gave him the new role.

Worked a treat.

Thanks to all.