Re: Granting permission to a database - need help

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

Learner (pradev@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, esquel@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

.

Relevant Pages

  • Re: Granting permission to a database - need help
    ... Windows authentication, and not use an application login, as then ... Links for SQL Server Books Online: ... USE Driver Access ...
    (microsoft.public.sqlserver.programming)
  • 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: 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)