0

We have a situation where we have a Windows Application connecting to SQL Server using Windows Authentication (similar to How to add Active Directory user group as login in SQL Server). All the Active Directory users who will be using the application have been added to an AD group (let's call it GroupX)

A login was then created on the SQL instance for GroupX. Only the "public" server role has been assigned to this login. On the database itself a user was created for GroupX. The "db_Owner" role permission was allocated to the this group user. We need "db_Owner" permissions as the application needs read/write access as well as permission to do schema changes.

When the desktop application attempts to connect to the database we get an exception: "Cannot open database z requested by the login. The login failed. Login failed for user y.

The area of SQL Security is a bit new to me as I am used to being a sysadmin on my own SQL instance against which I develop and off course in that kind of environment everything just works.

Any ideas will be greatly appreciated.

Community
  • 1
  • 1
CodeQuest
  • 1
  • 1

1 Answers1

0

Can you ensure that the db_owner permission shows up for the user group in the database?

Go to SQL Server >> Security >> Logins and right click on Group X and select Properties

In newly opened screen of Login Properties, go to the “User Mapping” tab. Then, on the “User Mapping” tab, select the desired database – especially the database for which this error message is displayed. On the lower screen, check the role db_owner. Click OK.

Source: http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
  • Change `NT AUTHORITY\NETWORK SERVICE` to `GroupX` and this would be a good approach to see if the users is mapped correctly. – JodyT Mar 08 '13 at 09:03
  • @JodyT Thanks! Done. I just copied the soltution from the link, and didn't think of updating it. – SchmitzIT Mar 08 '13 at 09:07
  • Thanks for all the input. I have checked now under User Mappings for the login and can confirm that "db_owner" is indeed selected as well as "public". I have also checked the properties of the "public" server role now to make sure that Connect permissions have not been revoked for this role. One thing I have noticed however is that the properties window from the client only shows Endpoints whereras on my machine there is a node for Servers as well. The only way for users to currently connect to the databases is to give the Group sysadmin privileges which does not make sense. – CodeQuest Mar 11 '13 at 11:00