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'
   go
   USE DriverAccess
   go
   CREATE USER DAUser FOR LOGIN DAAplication
   go

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

   CREATE LOGIN [Domain\Somegroup] FROM WINDOWS
   go
   USE Driver Access
   go
   CREATE USER [Domain\Somegroup]

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

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hello Erland,

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

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

CREATE LOGIN [Domain\Somegroup] FROM WINDOWS
go
USE Driver Access
go
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?

CREATE LOGIN [Domain\Somegroup\User] FROM WINDOWS
go
USE Driver Access
go
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
http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=395. 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.

------------------------------
ADDITIONAL INFORMATION:

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.

Thanks,

L
.



Relevant Pages

  • Re: ODBC worked until Server reboot defaulted to windows authentic
    ... I got the exact spelling of the windows login with the case and I rebooted ... The odbc source is set up in windows and that connects on the test. ... The SQL Server Mgmt studio connects also but only as Windows authentication. ...
    (microsoft.public.sqlserver.setup)
  • Re: login 101..
    ... On Windows 2003, SQL Server 2005 can enforce the Windows password complexity ... Windows authentication - SQL Server uses a special protocol to ask ... user is in the list of allowed logins, ...
    (microsoft.public.sqlserver.security)
  • Re: Granting permission to a database - need help
    ... folder and it would have the database connection info. ... Windows authentication, and not use an application login, as then ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • RE: How to create a trusted connection
    ... You need to grant access for the Windows login by referring to the books ... is set to use Windows authentication to be able to do trusted connection. ... There are two modes of authentication in SQL Server: ...
    (microsoft.public.sqlserver.security)
  • Re: Slow booting xp home.
    ... Changing the boot order to boot first from your hard disk might save you a half second, but you won't be able to boot from a CD until you change it back - and the time spent to do that will erase any previous time saved. ... 2- Consider what software you really want to start with Windows and also how you've configured your applications at startup ... Installing and Registering Visual Studio Express Editions Smart Device ...
    (microsoft.public.windowsxp.perform_maintain)