SQL Server – How to Change Default Security for Database Access

permissionsSecuritysql serverssms

I have a database hosted on my machine that I want co-workers to be able to access and edit through a program I created.

My current connection string is:

Server=MyMachine;Database=MyDatabase;Integrated Security=True;

And to let another user access it I have to:

  1. Add a user under database security in SQL Server Management Studio
  2. Add a user under server security in SSMS

  3. Run the script:

    USE Database
    GRANT SELECT, INSERT, UPDATE, DELETE TO [Username]
GO

How can I configure it so that everyone on my work network (or everyone whose windows username starts with "MyCompany\") can have these privileges by default? That way I don't have to manually add every new person.

Best Answer

You could create a domain group that you could use to create as login and user on your SQL Server and YourDatabase. After creating the domain group, you could do something like:

USE [master]
GO
CREATE LOGIN [DOMAIN\AllUsersForYourDatabase] FROM WINDOWS
GO
USE [YourDatabase]
GO
CREATE USER [DOMAIN\AllUsersForYourDatabase] 
FOR LOGIN [DOMAIN\AllUsersForYourDatabase] 
GO

You can then make the logins on your domain members of that group. All the logins will inherit the permissions granted to the group that gives them access. (You could include Domain Users in the group if you truly wanted everyone in the domain to have access.)

Of course, you need to grant the needed rights to the YourDatabase user [DOMAIN\AllUsersForYourDatabase] so that everybody in the group gets the same rights.

If you need some users to have additional rights, those can be individually granted. Or you can create another Group that would include only the users with the extra rights. The users will get the aggregate of all rights from all groups for that login.

Related Question