what user membership to assign?

Question

what user membership to assign?

Senchi

Ten Centuries

Points: 1184
More actions
January 14, 2017 at 11:30 am

#320571

I have a lot of stored procedures. Now, every time I add a user I must go through each one of them and assign permissions manually.
What permission must I give users globally so I don't have to do that ?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Thom A SSC Guru Points: 99129 More actions · Answer 1

Senchi - Saturday, January 14, 2017 11:30 AM
I have a lot of stored procedures. Now, every time I add a user I must go through each one of them and assign permissions manually.
What permission must I give users globally so I don't have to do that ?

Create a role on the database, and grant that role execution rights on the the SPs that you want these users to be able to use, and add the users to that role. Then you need only add the user to the role each time, rather than give them permissions to every SP.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 2

A better thing to do, in many cases, is to use Windows Active Directory Groups. Then, you only need to assign the group privs once in SQL Server since the users that belong to the group will come and go naturally as they are added or disabled/dropped in Windows, which should be done anyway.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Senchi Ten Centuries Points: 1184 More actions · Answer 3

actually, it seems a better option to make all new users database owners and only if you need to deny them something you do that on the object in question. Am I right ?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 4

actually, it seems a better option to make all new users database owners and only if you need to deny them something you do that on the object in question. Am I right ?

No. By granting them db_owner you are giving them full db rights. So you are basically saying that you trust every user you are granting permissions to the database implicitly. Not only are you trusting them with the data they can add/modify/delete, you are trusting them with being able to modify/drop/add db objects as well. Moreover db_owner has the grant option and can essentially override any deny permissions you assign.

Best to setup a database role, add the permissions you want to the role and then add users to the role. If the database users are tied to Windows logins you can do as Jeff suggested and add a Windows group to the role so all that needs to be done is that new users get added to the correct Windows group.

If you want (and are able to) take it a step further, you can create a new schema and add stored procedures to the schema. Then the database role would just need execute rights granted on the schema. That way whenever new stored procedures get added to the schema the db role does not need to be modified.

Joie Andrew
"Since 1982"

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 5

Senchi - Saturday, January 14, 2017 11:36 PM
actually, it seems a better option to make all new users database owners and only if you need to deny them something you do that on the object in question. Am I right ?

Absolutely not. In fact, if it were up to me, users wouldn't have any privs... not even read privs. The only privs I'd give them or applications would be the privs to run certain stored procedures. All users and applications would be relegated to only the normal PUBLIC role.

Of course, that would cause all of the front end programmers to come apart at the seams because the ORMs wouldn't actually work. Everything would have to call a stored procedure. That's not such a bad idea when it comes to performance either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99129 More actions · Answer 6

Senchi - Saturday, January 14, 2017 11:36 PM
actually, it seems a better option to make all new users database owners and only if you need to deny them something you do that on the object in question. Am I right ?

As the others have said, this is an awful idea. Your users would be able to effectively do anything on your databases. And, because you've given all of them the db_owner role, if two of them got together they could REVOKE any DENY permissions for each other, giving them access to EVERYTHING in that database.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Senchi Ten Centuries Points: 1184 More actions · Answer 7

I think adding a new role is very good idea.
How can I list users that are a members of my new created role so not to loose track ?

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 8

Senchi - Sunday, January 15, 2017 7:04 PM
I think adding a new role is very good idea.
How can I list users that are a members of my new created role so not to loose track ?

EXEC sp_helprolemember '<rolename>';

Joie Andrew
"Since 1982"

Senchi Ten Centuries Points: 1184 More actions · Answer 9

Joie Andrew - Sunday, January 15, 2017 7:15 PM
EXEC sp_helprolemember '<rolename>';

everyone's telling me these builtin stored procedures are deprecated (?)...how can I write my own so only membername shows ?
alos running this procedure on server gives me desired results. However from my program I get only the current user thats logged in.
Thank you Joie

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 10

Senchi - Sunday, January 15, 2017 7:36 PM
Joie Andrew - Sunday, January 15, 2017 7:15 PM
Senchi - Sunday, January 15, 2017 7:04 PM
I think adding a new role is very good idea.
How can I list users that are a members of my new created role so not to loose track ?
EXEC sp_helprolemember '<rolename>';
everyone's telling me these builtin stored procedures are deprecated (?)...how can I write my own so only membername shows ?
Thank you Joie

Well, I do not believe that particular stored procedure is deprecated. If it was it would say so, usually at the top of the article for the command.

sp_helprolemember (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms178021.aspx

However you can query sys.database_role_members and sys.database_principals to find out the role member as well. This is an example straight from Microsoft's documentation:

SELECT DP1.name AS DatabaseRoleName, isnull (DP2.name, 'No members') AS DatabaseUserName FROM sys.database_role_members AS DRM RIGHT OUTER JOIN sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id LEFT OUTER JOIN sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id ORDER BY DP1.name;

sys.database_role_members (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms189780.aspx

Joie Andrew
"Since 1982"