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 12»»

How do I allow all logged in users to access a SQL Express 2008 R2 database? Expand / Collapse
Author
Message
Posted Tuesday, July 9, 2013 10:23 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1471731
Posted Tuesday, July 9, 2013 10:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 12,895, Visits: 32,093
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son


  Post Attachments 
Add_Windows_Group.png (115 views, 210.66 KB)
Post #1471738
Posted Tuesday, July 9, 2013 10:44 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1471744
Posted Tuesday, July 9, 2013 10:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 12,895, Visits: 32,093
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471747
Posted Tuesday, July 9, 2013 1:22 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1471819
Posted Tuesday, July 9, 2013 1:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 4:16 PM
Points: 31,018, Visits: 15,456
The logins need to be mapped to users in each database, and a role set there.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1471824
Posted Tuesday, July 9, 2013 1:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 PM
Points: 12,895, Visits: 32,093
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1471828
Posted Monday, July 15, 2013 11:05 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1473793
Posted Monday, July 15, 2013 1:40 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1473873
Posted Monday, July 15, 2013 2:40 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, June 20, 2014 8:23 AM
Points: 738, Visits: 1,305
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.
Post #1473900
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse