SQL assign read/write access to all users on PC

Question

I'm installing an SQL database and our application on a single PC. Under Vista this needs to be installed by an administrator account, but this means that the regular user account(s) can run our program, but it can't access the database it needs.

What I need is a simple way of allowing all NT logins on the PC to have access to the database.

I thought I'd solved it: In SSMS I can manually assign db_datareader/writer roles to BUILTIN\Users, and it works perfectly.

However, if my installer programmatically executes the following, it doesn't work:

USE [OurDatabase]
GO
EXEC sp_addrolemember N'db_datawriter', N'BUILTIN\Users'
GO
EXEC sp_addrolemember N'db_datareader', N'BUILTIN\Users'
GO

The users get assigned the roles, but they still can't access the database. The only difference I can see in SSMSE is that in the latter case, they also gain ownership of a new "BUILTIN\Users" schema, which I suspect may be screwing things up. Clearly SSMS does something slightly different...

Can anyone suggest a way to get this working programmatically?

Answer 1

What about setting the application only to run as administrator on vista?

Answer 2

In OurDatabase, what user is mapped to the login? Do you see a difference in the SSMS method vs. programmatic? What is the default schema for the user mapped to the group login when it works?

Answer 3

Ah, it appears that SSMS also does a CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] on the database if the user doesn't exist.

I was twiddling the permissions on and off several times while trying to work out what to do, and on subsequent attempts the user already existed so SSMS didn't bother with the CREATE command. So when I finally figured out what I needed to do, I was getting an incomplete (for my needs) script out of it.

This wasn't obvious because the effects of this command are not seen in the user properties; they appear in the User Mapping in the Server Security\Logins section of SSMS.

Thanks to Aaron (the mention of logins had me looking at the Security\Logins page and I spotted the difference in the mappings there)