Re: Granting permission to a database - need help

On Jan 5, 5:53 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Learner (pra...@xxxxxxxxx) writes:
Thanks for the inputs. But couldn't fix the issue I am facing with.
The reason why I wanted to create another user is because we have a
software that needs to be installed on users computers and this
software needs to  talk to our production SQL Server 2005 to access
its database called 'DriverAccess'. When the software is installed on
users computers it generates a .config file user the program files
folder and it would have the database connection info. Below is the
connection code snippet in the config file

Since this is a Windows application, there is all reason to use
Windows authentication, and not use an application login, as then
you cannot track who has done what in any way.) (It would be
different if it was a web app; in web apps it appears to be common
to use an application login.)

for some reason it doesn't connect to the database with this
connection info (is 'Integrated Security=SSPI' option tries to connect
using windows authentication?) .

So what happens? Has the Windows user you tried to login as, been
granted access to the database?

1).Under databases there is a Security/Logins folder I created a user
called DAApplication.
2).Under the DriverAccess database Security/users folder created a
user called DAUser.

To which login did you connect it to?

In any case, I would recommend you from clicking around in SSMS, because
it will only serve to make you more confused. And you cannot automate
points and clicks. Use T-SQL commands instead.

The T-SQL steps for the above would be:

   CREATE LOGIN DAApplication WITH PASSWORD = 'VerY_$$$$Tron6'
   USE DriverAccess

However, you should really use Windows authentication, as Steen said,
this can be granted for an entire Windows group:

   USE Driver Access
   CREATE USER [Domain\Somegroup]

Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:
SQL 2005:
SQL 2000:

Hello Erland,

Thanks for detailed inputs. Like you explained the windows
authentication option sounds more appropriate as this is a desktop

However, you should really use Windows authentication, as Steen said,
this can be granted for an entire Windows group:

USE Driver Access
CREATE USER [Domain\Somegroup]

I am not sure how it works but we authenticate users via Active
Directory. When you said Windows is it necessarily an Active Directory
or some thing else? If it is an Active Directory then can I use the
above sql to create individual (I will try with just one user first
and if it works out then based how many users need access I will
create a group) logins just for the users that need access to our
desktop application/tool?

USE Driver Access
CREATE USER [Domain\Somegroup\User]

Comming back to Sql Server user account I used the Management studio
to create a user (from reading your response though this wasn't what
you think would be choice to create logins but created it yesterday so
wanted to mention it here to know how Sql server logins work) called
DAUser ( I deleted all the previous accounts that I created and it is
a brand new one) following the instructions provided in the article After
creating the login I closed the Management studio and tried to log in
using the user id DAUser and the <password> but I got this message

TITLE: Connect to Server

Cannot connect to FAYSQLSERV.


A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.)
(Microsoft SQL Server, Error: 233)

Can please provide your inputs on this as well.