Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Windows Authentication Expand / Collapse
Author
Message
Posted Thursday, September 17, 2009 10:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:40 PM
Points: 4, Visits: 13
Hello,
I'm fairly new to how Windows Authentication works with SQL SERVER 2005 so please bear with me.

I developed a VB inventory control application in Visual Studio 2008 that accesses an SQL SERVER 2005 database. The SQL SERVER 2005 INSTANCE is located on a Windows 2003 Server that is a member of a Domain. Currently only one user will execute this application but I would like to setup a Group that has read/write access to the database.

I'm not sure exactly how to set this up but I think generally I have to do the following:
1) Setup a Group Login Account to the Win 2003 Server
2) Setup the initial user as a member of the Group Login Account
3) Setup the Group Login Account in SQL SERVER (not sure how to do this)
4) Give the appropriate access rights to the Group Login Account in SQL SERVER (not sure how to do this).

I've read a bunch of manuals on this subject but I'm confused as to the "roles", user rights, etc. Can anyone help?
Post #789795
Posted Thursday, September 17, 2009 10:09 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:52 PM
Points: 3,428, Visits: 14,451
If there is an Asp.net application running all you need is a configuration and the .NET run time will take care of roles and permissions for you. Check below for details.

http://www.sqlservercentral.com/Forums/Topic779867-364-1.aspx


Kind regards,
Gift Peddie
Post #790099
Posted Thursday, September 17, 2009 10:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:40 PM
Points: 4, Visits: 13
The application is a Windows VB app developed in Visual Studio 2008 so I don't think your response applies.

Thanks for the response though, I really appreciate the thought!
Post #790118
Posted Thursday, September 17, 2009 11:57 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
In your AD create a group like "Site_DB_Auth" as an example.
Ad all the members to this AD group.
Add this group, and assign a role like "public" or whatever access you need them to have.
Map this Login Group to a DB

ok...lets start...follow step by step:


1. In SSMS, under Security,Logins,click "New Login".
2. Left of the "Login Name" text field, click "Search"
3. Change the "Location" to your domain, as it defaults to the local server.
4 In the "Enter the object nme to Select" field, type your group name eg. "Site_DB_Auth"
and click "Check Names".
It should resolve with an underlining.
5. Click "OK"

6. At the Bottom of the screen, select the drop down "Default Database" and select the DB this group needs to access by default.

7. Ok, now on the left menu, 2nd option is: "Server Roles"
Tick the appropriate boxes to assign the roles.

8. Ok, now the 3rd option "User Mapping", tick the box next to the DB you want this group to have access to.
Also below the DB list, is "Role Membership" - confirm the correct "db_*" access has been selected.
9. You should be done for a start.
10. Click OK

To confirm:

In your DB list on the left in SSMS, drop down your DB List, then the DB used, drop down Security, then drop down "Users" - check to see if your new AD group is in the list.


If I understood your query correct, then this should resolve the Authentication issue.




----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #790127
Posted Friday, September 18, 2009 6:59 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:52 PM
Points: 3,428, Visits: 14,451
george-1113455 (9/17/2009)
The application is a Windows VB app developed in Visual Studio 2008 so I don't think your response applies.

Thanks for the response though, I really appreciate the thought!


It means you either did not read or don't understand it that I was suggesting you use Winform application settings to create your roles and it uses the Asp.net roles framework to resolve your users.

If you don't want to do that then you need to create app.config and create configuration setting using something similar to the previous post. I am going to look for cool client stuff and post the link of how you create the app setting if you want to do it the hard way.

This is cool client stuff it is comprehensive way to define user permissions in Winform application the person left Microsoft so it has been simplified so any developer can use it without the need to contact that person.

http://blogs.msdn.com/rprabhu/articles/433979.aspx


Kind regards,
Gift Peddie
Post #790341
Posted Friday, September 18, 2009 9:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:40 PM
Points: 4, Visits: 13
Thank you so much for the beautifully detailed explanation! It worked like a champ! Just out of curiosity, would the same or similar procedure work for a Windows Work Group situation?

Anyway, thanks for the help!
Post #790466
Posted Friday, September 18, 2009 9:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 22, 2013 4:40 PM
Points: 4, Visits: 13
Thanks again for your response! You are correct about my not understanding what you meant. The app.config is actually created for you by Visual Studio and of course is modifiable with regards to the connection string. I'm pretty sure I understand how DB security and access works from the application. I'm just fuzzy on the SQL SERVER side of security setup for a Windows Domain group.

I'll do some further research on your ASP.net suggestion though. Again, thanks for the help!
Post #790479
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse