Re: Security Model Problems

From: Steve Thompson (stevethompson@nomail.please)
Date: 11/01/02 

From: "Steve Thompson" <stevethompson@nomail.please>
Date: Fri, 1 Nov 2002 13:56:07 -0500

Brian,

If you've installed SQL Server on a member server, then I'd recommend a
couple of changes to your process:

Step 3 Create two new local groups on the server hosting SQL Server (one for
each of the global groups) and add the appropriate Global Groups to the
local groups (on the server)

Step 4 Grant SQL Server login to the Local groups

Step 5 Assign the appropriate SQL Server database permissions to the local
groups

Steve

"Brian Cidern" <bcidern@gillespie.com> wrote in message
news:8c4d01c281ba$e80188b0$35ef2ecf@TKMSFTNGXA11...
> Some of the documents I've referenced (from MSDN) for
> setting up security within SQL Server made the following
> suggestions:
>
> 1. Security Mode: Windows Authentication
> 2. Create global domain groups and add appropriate users.
> 3. Create local domain group and add the appropriate
> global domain groups.
> 4. Grant login to the local domain group.
>
> Scenario.
> -- Win2k AD groups --
> Global Group: APP_read
> Global Group: APP_write
> Local Group: SQL_Logins
>
> SQL_Logins (members)
> APP_read
> APP_write
>
> (domain users added to the appropriate Global Domain
> groups)
>
> -- SQL Server --
> USE master
> sp_grantlogin 'DOMAIN\SQL_Logins'
>
> USE APP_db
> sp_grantdbaccess 'DOMAIN\APP_read'
> sp_grantdbaccess 'DOMAIN\APP_write'
> sp_addrolemember 'db_datareader', 'DOMAIN\APP_read'
> sp_addrolemember 'db_datawriter', 'DOMAIN\APP_write'
>
> ------------
> Now, when I try to connect one of the users (who has been
> made a member of either of the Global Domain groups, I get
> a Login Failed while trying to create/configure a User or
> File DSN.
>
> The only way I've been able to gain access from the
> clients was to explicitly grant login to the specific
> Global Domain group.
>
> Details:
> SQL Server 2000
> Version: 8.00.679
> Patch Level: SP2+Q316333
> The NetLib is set to TCP/IP.
> MDAC 2.62.7400.1 installed on client.
>

Relevant Pages

  • Security Model Problems
    ... setting up security within SQL Server made the following ... Security Mode: Windows Authentication ... Create global domain groups and add appropriate users. ...
    (microsoft.public.sqlserver.security)
  • Re: 2005 Cluster Install Error
    ... Domain Local Groups were introduced in Windows 2000 Active Directory ... > Resource groups, and, apparently, SQL Server 2005 security groups. ... there is a new requirement that not only does the Installation ... >>> 3.5.15 Corrections to the Names of Security Groups Created During Setup ...
    (microsoft.public.sqlserver.clustering)
  • Re: Sql server 2005 clustered service group
    ... For each clustered service in the instance of SQL Server that you are ... administrator for the names of existing domain groups, ... Note that SQL Server accounts will not be removed from the groups if SQL ... I see with installing SS2005 cluster if forces me to use domain accounts ...
    (microsoft.public.sqlserver.setup)
  • Re: 2005 Cluster Install Error
    ... There are DOMAIN Global groups, LOCAL groups, Cluster ... Resource groups, and, apparently, SQL Server 2005 security groups. ...
    (microsoft.public.sqlserver.clustering)
  • Re: Should you use local machine groups?
    ... No. Do not map domain groups to local accounts and then map those local ... Grant SQL Server login permission directly to the Domain accounts, ...
    (microsoft.public.sqlserver.security)
Quantcast