How do you handle logins and groups

Question

How do you handle logins and groups

Frank Kalis

SSC Guru

Points: 111183
More actions
July 28, 2003 at 6:54 am

#64797

Hi there,
where do you manage your logins?
Do you create and maintain NT groups and add these to SQL Server or
do you add every single login to SQL Server and maintain users and groups there?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

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

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

RonKyle SSC-Dedicated Points: 31556 More actions · Answer 1

Hi Frank,

We create the NT Groups, assign the groups to SQL Server with the permissions needed by the groups. The users are in each group are maintained by the system administrator.

As a best practice, using the SQL roles to create groups should only be used when you don't have control over the domain. Since we do, we use the method outlined above. I have found it makes cross database permissions much easier than they might otherwise be, particularly when the databases are on different servers.

Also, you don't have to be concerned when a user is no longer with you. It becomes an network admin function.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

Hi Ron,

quote:
We create the NT Groups, assign the groups to SQL Server with the permissions needed by the groups. The users are in each group are maintained by the system administrator.
As a best practice, using the SQL roles to create groups should only be used when you don't have control over the domain. Since we do, we use the method outlined above. I have found it makes cross database permissions much easier than they might otherwise be, particularly when the databases are on different servers.
Also, you don't have to be concerned when a user is no longer with you. It becomes an network admin function.

yup, forget to mention that the whole thing is based on NT authentication.

I now see the advantage on NT Groups over SQL roles. Especially that the burden of maintenance is on someone else's shoulder

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

jlcline Right there with Babe Points: 786 More actions · Answer 3

I think about this topic every day. We have a mixed bag of NT and Unix users across several domains, Sql Server and Oracle databases, web applications and an LDAP server. Our end user will always use a web application to access data. Therefor we create database logins/users for our applications and not for each end user. We have roles within each web application and the application admin assigns an end user to roles. We authenticate end users against our LDAP server(single password for multiple applications). The group that we wrote the application for is responsible for adding and removing users.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Hi Jody,

quote:
I think about this topic every day. We have a mixed bag of NT and Unix users across several domains, Sql Server and Oracle databases, web applications and an LDAP server. Our end user will always use a web application to access data. Therefor we create database logins/users for our applications and not for each end user. We have roles within each web application and the application admin assigns an end user to roles. We authenticate end users against our LDAP server(single password for multiple applications). The group that we wrote the application for is responsible for adding and removing users.

I'm in the lucky position to say that my Servers have

- no Web exposure

- no external exposure (nicely sitting behind a good firewall)

- no mixed environment (might be changing some day)

- only NT authentication

I started with creating logins for every single user and then aggregating them within SQL Server to groups, but I see, only for my needs, the obvious advantages of creating a NT group and only adding this group to SQL Server. So when someone leaves company, his NT user account is deleted (or deactivated?) and right at this moment so it is in SQL Server. No more orphaned logins.

As in your case, I hear our network admins trying to bring mainframe, linux and windows together

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]