Re: Help! Set up Windows Group to access application

Could you give me a concrete step-by-step example of what to type in Query
Analyzer to set up a new windows login and a new group and corresponding
rights?

Below is a general permission script that you can customize for your needs. The connection string for a trusted connection is:
"Provider=SQLOLEDB.1;Server=W2KDA1SQL;Database=PartiallyDisbursedLoanCalls;Integrated Security=SSPI"


USE PartiallyDisbursedLoanCalls

--add a new role
EXEC sp_addrole 'MyRole'

--grant all needed object permissions to role
GRANT EXEC ON dbo.usp_MyProc TO MyRole
GRANT SELECT ON dbo.MyTable TO MyRole


--add Windows login
EXEC sp_grantlogin 'MyDomain\CustomerService'

--add login as database user
EXEC sp_grantdbaccess 'MyDomain\CustomerService'

--add database user to role
EXEC sp_addrolemember 'MyRole', 'MyDomain\CustomerService'


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sandy" <Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:14E9358A-5531-435A-A9A6-71B5F0F4B13C@xxxxxxxxxxxxxxxx
Hi -

The connection string I am using is:
.ConnectionString =
"Provider=SQLOLEDB.1;Server=W2KDA1SQL;Database=PartiallyDisbursedLoanCalls;User Id=SqlUser;password=Password10"

The above string works to get the users into the database. They are
inserting records to their heart's content. The problem is when someone
tries to access any Crystal Report (I use stored procedures in Sql Server and
the corresponding VB code), they get a connection error.

I wanted to use Windows security, but I couldn't get it to work; even with
trying the suggestions from the people who have replied previously.

I also ended up using Sql Server 2000 because I needed to get this out in a
hurry and knew the SqlUser and password works with another database I have
connected to an application.

Could you give me a concrete step-by-step example of what to type in Query
Analyzer to set up a new windows login and a new group and corresponding
rights?

I am getting frantic at this point . . .

--
Sandy


"Erland Sommarskog" wrote:

Sandy (Sandy@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
> Thanks again for replying, Sue!
>
> I don't know exactly where to look for that. Could you point me in the
> right direction?
>
> Again, any help you can give me is greatly appreciated!

If you have troubles with finding your ways in Crystal Reports, you are
probably better of in a forum for Crystal Reports. While we use it in our
shop, I try to stay away from it as much as possible. (And we never connect
directly from Crystal to the database anyway. We pass it recordssets from
VB.)

> Another strange thing -- another developer was able to access the
> reports via the application without a problem.
>
> A regular user cannot and receives the error previously posted.

That certainly is a little funny, since it seems from your posts that
you are using integrated security in both Crystal and the VB app. But
that is only seems. It would really help if you posted the connection
strings that you use, both in the VB6 app and in Crystal.



--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx


.

Relevant Pages

  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... Create a Connection object and pass in a ConnectionString to ... manage the database table you reference. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: (Newbie)Application Roles
    ... level: the database itself. ... SQL Server accommodates these needs through the use of application ... the user's connection through a specific application. ... the connection permanently loses all permissions applied to ...
    (microsoft.public.sqlserver.security)
  • Re: My Book is Incomplete Regarding SQL Commands ;-(
    ... Visual Studio but not to VS 2003. ... Create a Connection object and pass in a ConnectionString to address ... manage the database table you reference. ... Yep, my latest book can help too, but mostly if you're targeting SQL Server ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help! Set up Windows Group to access application
    ... Analyzer to set up a new windows login and a new group and corresponding ... The connection string for a trusted connection is: ... tries to access any Crystal Report (I use stored procedures in Sql Server ... If you have troubles with finding your ways in Crystal Reports, ...
    (microsoft.public.sqlserver.security)