How do I allow all logged in users to access a SQL Express 2008 R2 database?
-
I'm working on a new WPF application, which uses a SQL Server 2008 R2 Express database, stored locally on the user's machine. In testing it has worked fine for me (naturally), but when users have tried to use it, it has failed. I've contacted Microsoft Tech support, and they've found that the issue is when trying to access tables in the SQL Express database. In looking at the connection strings, I've found that I'm using integrated security. I'm sure I did that intentionally, under the assumption that it would work fine for all our users. Well, that's proven to be wrong, at this point.
So the way I see it I could do one of two things:
1) I need to find a way so that whenever a user accessing the SQL Express database, through the application and using integrated security, it will allow any and all logged in users access to the tables, views, stored procedures and triggers. How do I do that, in SQL Express?
2) I could just scrap the whole idea of using integrated security to using a SQL user instead, and change the connection strings. One uses System.Data.SqlClient, which is an assembly in .NET. This string is easily changed. The other one is harder, because I'm using Entity Framework to also access the database. In looking at that connection string it too uses integrated security, but that connection string is much more complicated than the one for System.Data.SqlClient, so I'm a little less inclined to change it to using a SQL profile/user. However, if it would be better to use a SQL user, then I'll find a way to change that connection string.
Bottom line: which of the 2 options I've listed should I use?
Kindest Regards, Rod Connect with me on LinkedIn.
-
i think the only thing missing is for you to find a domain group that everyone belongs to, like domainname\Domain Users, and add that as a login, and then as a user to the right databases, and grant that windows group the right permissions.
then everyone who belongs to that AD group would have access...it's up to you to limit them as far s read/write.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Thank you for your reply. So are you suggesting I go with option 2, use a SQL user instead of integrated security?
Kindest Regards, Rod Connect with me on LinkedIn.
-
no not at all.
I'm suggesting to continue to use integrated security.
it seems to me, that you planned on setting it up that way, but the piece that is missing is assigning a windows group to have permission to the "right" database on your Express instance.
I'm thinking you glossed over the permissions on the server issue, probably inadvertently?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
OK, I've added our Domain Users group to the logins for SQL 2008 R2 Express on my PC. It's server role is public. Domain User's default database is the database I'm trying to access. But it still fails. What am I leaving out?
Kindest Regards, Rod Connect with me on LinkedIn.
-
The logins need to be mapped to users in each database, and a role set there.
-
Rod at work (7/9/2013)
OK, I've added our Domain Users group to the logins for SQL 2008 R2 Express on my PC. It's server role is public. Domain User's default database is the database I'm trying to access. But it still fails. What am I leaving out?well adding a user and granting permissions are a three part process.
first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;
users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat the same steps(except if the login exists)
here's an example of creating a role,and giving that role Read access to the tables, and also execute permission to stored procs and functions you created in that specific database.
in this example, my domain users have a ton of rights: read/write/execute, and also create/modify objects...which might be too much, depending on your applciaiton..this is a model, and not the ten commandments or anything.
USE [SandBox] --my db for users to do stuff.
CREATE ROLE [DomainUsersAccess]
--give my new role appropriatepermission to ALL tables
EXEC sp_addrolemember N'db_datareader', N'[DomainUsersAccess]'
EXEC sp_addrolemember N'db_datawriter', N'[DomainUsersAccess]'
EXEC sp_addrolemember N'db_ddladmin', N'[DomainUsersAccess]'
-
GRANT EXECUTE TO [DomainUsersAccess]
now that the role is created, , and you already added the LOGIN to the master database, we can add the windowsgroup as a USER in the database:
USE [SandBox];
ADD USER [Domain\Users] FOR LOGIN [Domain\Users]
and now that the user group exists, we add that user to the role we created with the appropriate rights already decided upon.
EXEC sp_addrolemember DomainUsersAccess', N'Domain\Users'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell,
Well, as it turns out other priorities demanded my time, so I'm only now getting back to this. Here's the SQL Script that I came up with, based upon what you gave me last, to create the DomainUsersAccess role:
USE ASI
GO
CREATE ROLE [DomainUsersAccess]
--give my new role appropriatepermission to ALL tables
EXEC sp_addrolemember N'db_datareader', N'[DomainUsersAccess]'
EXEC sp_addrolemember N'db_datawriter', N'[DomainUsersAccess]'
--grant ability to run stored procedures
GRANT EXECUTE TO [DomainUsersAccess]
--The login [OUR_DOMAIN\Domain Users] was already added, but that
--may be possible to script here as well.
--add the [OUR_DOMAIN\Domain Users] group as a user to ASI
CREATE USER [OUR_DOMAIN\Domain Users] FOR LOGIN [OUR_DOMAIN\Domain Users]
--add the Domain Users group to the DomainUsersAccess role
EXEC sp_addrolemember N'DomainUsersAccess', N'OUR_DOMAIN\Domain Users'
Unfortunately, I got an error message while running it. Here's the error:
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '[DomainUsersAccess]' does not exist in this database.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '[DomainUsersAccess]' does not exist in this database.
I'm not sure why it's complaining about sp_addrolemember, as the stored procedure looks to me to be accepted in the query window in SSMS. Any ideas?
(And something else I don't understand; why does it complain about "line 75" when the script only has 20 lines of code in it?)
Kindest Regards, Rod Connect with me on LinkedIn.
-
Some more info. I ran the SQL script attached to SQL Express using my Windows account. My account has the SysAdmin role assigned to it, so I would think it would be able to create the DomainUsersAccess role.
And yet, it isn't in my instance of SQL 2008 R2 Express. Why is that?I'm sorry, I was wrong. The DomainUsersAccess role does exist in the ASI database in 2008 R2 Express.It doesn't look to me as though roles like db_datareader or db_datawriter have been added to the DomainUsersAccess role in the ASI database, though.
Kindest Regards, Rod Connect with me on LinkedIn.
-
OK, without a doubt I have determined that the lines causing me problems are:
EXEC sp_addrolemember N'db_datareader', N'[DomainUsersAccess]'
EXEC sp_addrolemember N'db_datawriter', N'[DomainUsersAccess]'
Those 2 lines generate the error messages:
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '[DomainUsersAccess]' does not exist in this database.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '[DomainUsersAccess]' does not exist in this database.
I just tried running those 2 lines of code again, and it failed with the same message. I don't understand why, because I'm running them connected to the database (ASI, in this case) where the DomainUsersAccess role exists. And I think I now know where that "Line 75" in the error message is coming from. I believe it's from the sp_AddRoleMember stored procedure; it's not from my 2 lines of SQL script.
Kindest Regards, Rod Connect with me on LinkedIn.
-
OK, I've discovered that my problem , with the sp_AddRoleMember stored procedure, was using both [] and ''. What I've done is this:
EXEC sp_addrolemember N'db_datareader', N'DomainUsersAccess'
GO
EXEC sp_addrolemember N'db_datawriter', N'DomainUsersAccess'
GO
Kindest Regards, Rod Connect with me on LinkedIn.
-
I just found out that because I'm using a bulk copy, which apparently requires ALTER TABLE permissions, I also had to include the db_ddladmin role. Who knew (I certainly didn't, until about 15 minutes ago.)
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply