June 27, 2019 at 12:55 pm
I've been searching all over for a script that will allow me to create a new login by giving it an existing login to generate all the database mappings and associated permissions. The existing user could be a member of database roles or have specific object permissions within various databases. Does anyone have a script or other method to do this so it doesn't have to be manually done using the GUI?
June 27, 2019 at 1:36 pm
such script should take into account that the existing user can have additional rights/restrictions being a member of AD groups which also have access to the SQL server and appropriate rights
June 27, 2019 at 2:37 pm
First, this why you want roles and/or groups in play. If this is SQL auth, AD groups don't help, but for Windows auth, this us why you'd want a group, even for one user. I'd urge you to move to groups/roles now as you do this to make this easier in the future. If a 2nd person needs this, a 3rd will.
For the users, we have a number of scripts here: https://www.sqlservercentral.com/search/generate+user+permissions/post_types/ssc_script One of them likely does what you need, but you'll have to look through them. Use a loop to run the script across all databases.
For server level permissions, I don't see anything, so it would have to be written. Essentially searching thro
June 27, 2019 at 2:55 pm
Steve - Thanks for the reply. There is a ton of helpful information in those search results. I should be able to piece enough together to make it work. Can you elaborate on what you mean by roles/groups? We utilize database roles which specify what objects to give permissions to. However, a new user might not need access to every database where the role exists. To complicate it even more there might only be one member of the role that needs additional permissions on objects outside of what the role is given. That's why I was thinking giving an existing user to model after would be best.
June 27, 2019 at 3:48 pm
The login to user issue is a tough one. SQL doesn't do a good job of modeling a user and then assigning database access. I think in this case, you do need a script that assigns the roles (and user mapping) in those databases that user A has to match up with user B.
One user in a role is not a reason to avoid this. That's the idea that gets us into trouble when User A quits and someone deletes them before you create User B. It also creates extra work when User B comes along, which almost always happens at some point. The extra work at the beginning is "Create role" (or "create server role") and Alter role ... add member. After that, there's no additional work, but down the road you always end up spending much more time duplicating someone.
June 27, 2019 at 5:56 pm
...Can you elaborate on what you mean by roles/groups? We utilize database roles which specify what objects to give permissions to. However, a new user might not need access to every database where the role exists. To complicate it even more there might only be one member of the role that needs additional permissions on objects outside of what the role is given...
To build on what Steve said, a common strategy I use is to have groups in Active Directory based on what department or job function the person has in the business, and then have roles inside the database for permissions on related items needed to accomplish some work. Roles are at the database level not instance level, so you would assign an AD group to a role in each database that group needs it. For example, I work at an insurance company. There is a separate Customer Service department and Claims department. The Claims department may need read permissions to see some aspects of the customer's policy maintained by Customer Service.
Additional permissions can be given to a member of the AD group directly even if that user only has database login access through the AD group, so that should handle the special case users.
June 27, 2019 at 7:01 pm
Thanks for the information. It sounds like groups are the way to go even if at the time it only ends up being one person.
June 27, 2019 at 10:24 pm
I'd be curious how to design this, or if you track notes, this might make an interesting article about permission changes, or evolution. Happy to work with you. Or if you want to post anything back here, I'm curious what you do.
I've often created a group (or role) for one person and then been grateful a year or two down the road when that person moves on or a second person does that job.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply