Re: No users/groups can login to SQL Server unless part of sysadmin

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

To make your Domain Users be able to connect to your SQL Server database, you need to create a Login for your Domain Users Domain Group and then you need to map it to the related database with datareader and datawriter role memberships.

- Open up SSMS
- Go to Security node and then to Logins
- Right click on Logins node and then click New Login
- On the New Login window click Search to find Domain Users group (tip: Groups should be selected from Object Types, otherwise Domain Users group will not be found)
- Then select the default database for this Login below on the New Login window.
- Go to User Mapping pane from the left list on New Login window and select appropriate database(s) from "Users mapped to this login" list and make selected the database from the same list then choose "db_datareader" and "db_datawriter" from the roles from the below list.
- Click OK to create the Login for Domain Users group and user(s) for this Domain Group in the selected databases.

--
Ekrem Önsoy



<spiyush12@xxxxxxxxx> wrote in message news:19cb6454-d1b3-4ec9-b745-d5057a9c63ae@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 29, 10:25 am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxxxxxxxxxxxxxxxx> wrote:
<spiyus...@xxxxxxxxx> wrote in message

news:335de5d9-48d0-46b3-8bd3-e7b2a13a1d2d@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

> We have SQL server 2005. We have mixed mode authentication.. We have
> created groups in Active Directory. I created a login for the group
> and mode is Windows authentication.

> When I create ODBC connection is does not let me select any database.
> However if I specify the group as part of sysadmin then I can connect
> to the server/database/tables...

> I am new ot SQL Server.. Any help would be appreciated..

To use Windows Authentication, assign a database role to the AD group. No
need to use SQL logins. All members of the AD group will get the permissions
assigned to the database role. I create my own role in the database and
assign the permissions needed. See Books On Line for details.

--
Richard Mueller
MVP Directory Services
Hilltop Lab -http://www.rlmueller.net
--

I went to the database--->Security--->Roles--->database Roles ---
db_accessadmin and added my self... I removed the sysadmin from
Server level security for my ad group..
It does not let me in or any other user in...

Help!!!

.

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: {permissions} How should I do this?
    ... We are using Sql Server 2005. ... Our database server is a win2k3 machine. ... that he has access to everything Domain Users has access to. ... If that account is suppose to be of use on a machine it will need to be ...
    (microsoft.public.windows.server.security)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)