-1

we have an SQL Server called sql01. On this,we have attached a number of databases, including the SSRS report server, which people can access. I have created a new database called ToolingSystem and added some data into table in it. I have also written an application that access this database using the following connection string:

Data Source=sql01;Initial Catalog=ToolingSystem;Integrated Security=True

But no one is able to see the data but me and the people in my IT group. Is there something I need to do in order to allow people to connect to this server/database (I don't know which). They are getting a "Login failed for user 'username'". What i'm looking for is people in our domain to see this information. It seems they can see the server (like the reporting stuff) but not my new database

I created a user for the database and am using it in the connection string but its still not letting me log in.

migrated from stackoverflow.com Jul 8 '15 at 16:39

This question came from our site for professional and enthusiast programmers.

  • Check if the users has access to the database. In SQL Management Studio, right click over database name, properties - permissions – Ricardo Pontual Jul 8 '15 at 13:54
0

What you need is:

  • A Login for the User at Server Level (Should already exist if people work with other databases on this Server)
  • A user in your new db, which is mapped to the login

example: when you have windows authentication as login for your users, it can look like this:

USE yourDB
GO

CREATE USER [JohnDoe] FOR LOGIN [YOURDOMAIN\John.Doe]

[JohnDoe] the can be added to the roles he needs (db_datareder, whatever...)

0

Here is a script to set up login, user, role, grant rights. I like to match the user name to the domain name, so it's obvious what is going on. This requires square brackets. This method will allow your users to connect directly to the database with excel if they have network access to it. You can look into application roles to restrict those shenanigans, I think.

CREATE LOGIN [DOMAIN\UserGroup] from WINDOWS;
USE ToolingSystem;;
CREATE USER [DOMAIN\UserGroup] from LOGIN [DOMAIN\UserGroup];
CREATE ROLE ToolingSystemUserRole;
exec sp_addrolemember 'db_datareader','ToolingSystemUserRole';
--exec sp_addrolemember 'db_otherroles','ToolingSystemUserRole';

exec sp_addrolemember 'ToolingSystemUserRole','DOMAIN\UserGroup];

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy