Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Logins / Users Question Expand / Collapse
Author
Message
Posted Tuesday, February 18, 2014 3:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 90, Visits: 176
We have several environments that have distinct AD login principles for each environment (e.g. COMPANY\user_basic_dev or COMPANY\user_basic_prod).

I order to simplify post release database comparisons between environments and refreshing development environments, I am proposing the following;

1. Move all explicit object permissions to role(s)

2. Generalise database users names so they are consistent between environments (e.g. user_basic) and then link this database user to the login principles (i.e. in the dev enviornment, the login COMPANY\user_basic_dev links to database user "user_basic".) Database users do not have associated schemas.

I have read around regarding this second point, looking for best practices but cannot find anything to suggest this is poor form. Historically, I have always assocated the login with a database user of the same name.

Has anyone got any experience to advise against this?

Ta in advance.



Post #1542439
Posted Tuesday, February 18, 2014 3:03 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:02 AM
Points: 823, Visits: 753
Johnny Ed (2/18/2014)
We have several environments that have distinct AD login principles for each environment (e.g. COMPANY\user_basic_dev or COMPANY\user_basic_prod).


Are these groups or logins in the AD?


1. Move all explicit object permissions to role(s)


That is probably a good thing


2. Generalise database users names so they are consistent between environments (e.g. user_basic) and then link this database user to the login principles (i.e. in the dev enviornment, the login COMPANY\user_basic_dev links to database user "user_basic".) Database users do not have associated schemas.

I have read around regarding this second point, looking for best practices but cannot find anything to suggest this is poor form. Historically, I have always assocated the login with a database user of the same name.


I am not sure that I see the point with this?

My gut reaction is that it cause confusion for the next guy when you have moved on in your career.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1542768
Posted Thursday, February 27, 2014 10:42 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, December 5, 2014 9:21 AM
Points: 776, Visits: 337
Johnny Ed (2/18/2014)
We have several environments that have distinct AD login principles for each environment (e.g. COMPANY\user_basic_dev or COMPANY\user_basic_prod).

I order to simplify post release database comparisons between environments and refreshing development environments, I am proposing the following;

1. Move all explicit object permissions to role(s)

2. Generalise database users names so they are consistent between environments (e.g. user_basic) and then link this database user to the login principles (i.e. in the dev enviornment, the login COMPANY\user_basic_dev links to database user "user_basic".) Database users do not have associated schemas.

I have read around regarding this second point, looking for best practices but cannot find anything to suggest this is poor form. Historically, I have always assocated the login with a database user of the same name.

Has anyone got any experience to advise against this?

Ta in advance.


Your #1 is a widely accepted best practice.

Your #2 is wonderful -- I love it! We do substantially that at my place of work, with domain group users like:


DOMAIN\dbname_RO (db_datareader)
DOMAIN\dbname_RW(db_datareader, db_datawriter)
DOMAIN\dbname_RWX (db_datareader, db_datawriter, db_executor [a user defined role with SP execute permissions and RO])
DOMAIN\dbname_DBO (db_owner)


This makes it extremely easy to know what domain users have what permissions. We even have a query to create dsadd commands to create the domain groups we can pass on to the network admins and also to set up the user defined role and the GRANTS.

When a domain login needs permissions to a database, the security requests merely join the login to the AD group.

Benefits:

1. You eliminate the need to set up specific user accounts as database logins, and even better, the need to clean up when a AD login is dropped for things like employee terminations.
2. Auditing becomes much easier.

Thanks
John
Post #1546009
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse