September 17, 2009 at 10:14 am
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?
September 17, 2009 at 10:09 pm
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
September 17, 2009 at 10:54 pm
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!
September 17, 2009 at 11:57 pm
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.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 18, 2009 at 6:59 am
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
September 18, 2009 at 9:16 am
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!
September 18, 2009 at 9:27 am
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!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply