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