July 26, 2017 at 11:08 am
Hi,
I need to create bunch of logins for a department employees. Is there a way I can create a group and assign all these users to that group and give the required pemissions for this group, like how it can be done for windows users?
Thanks.
July 26, 2017 at 11:27 am
Do you have windows authentication turned on on the server? If so, just add the windows group instead of the individual users.
Otherwise, you would want to create a SQL role, give the permissions to the role and then create the SQL logins and users and assign the users to the role you created.
SQL Roles can be applied at the instance level or the database level. So create the role at the level you need and put users into the role and then give the role the permissions you need.
You can put a windows group into a SQL Role too. There are a bunch of ways of doing this. Where I work, we have a mix of windows groups, non-group users and roles.
I believe that the recommended way to do things is to use either windows users or groups for providing access to the database and then roles for managing the permissions. This is the easiest for managing permissions for you can have multiple users or logins per role. So you can assign several windows users and groups to a single SQL role and manage permissions of the role.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 26, 2017 at 11:30 am
yes of course; what you want is to create a role. assign specific permissions to the role,and then add the users to the role, regardless of whether they are SQL or windows users or windows groups.
don't give built in roles unless you are sure they persons in the role need access to every table.
a couple simple examples:
USE SandBox;
GO
CREATE ROLE FinancialAnalysts;
GRANT SELECT ON dbo.Financials TO FinancialAnalysts;
GRANT SELECT ON dbo.FinancialDetails TO FinancialAnalysts;
GRANT EXECUTE ON rptFinancialsByMonth TO FinancialAnalysts;
GRANT EXECUTE ON rptFinancialsByYear TO FinancialAnalysts;
CREATE USER FinApp FOR LOGIN FinApp
CREATE USER [mydomain\lowell] FOR LOGIN [mydomain\lowell]
EXECUTE [sys].[sp_addrolemember] @rolename = FinancialAnalysts, @membername = FinApp;
EXECUTE [sys].[sp_addrolemember] @rolename = FinancialAnalysts, @membername = [mydomain\lowell];
CREATE ROLE DevelopersReadWrite;
GRANT VIEW DEFINITION TO [DevelopersReadWrite];
GRANT EXECUTE TO [DevelopersReadWrite];
EXEC sp_addrolemember 'db_datareader',DevelopersReadWrite;
EXEC sp_addrolemember 'db_datawriter',DevelopersReadWrite;
EXEC sp_addrolemember 'db_ddladmin',DevelopersReadWrite;
CREATE USER [mydomain\Developers] FOR LOGIN [mydomain\Developers]
EXECUTE [sys].[sp_addrolemember] @rolename = DevelopersReadWrite, @membername = [mydomain\Developers];
Lowell
July 26, 2017 at 11:30 am
Within SQL Server itself, no.
However, you could have your Domain Admin create a group at the Domain-level, add the requisite users to the group, then add the group as a login to SQL with the permissions required (we did this where I work due to near constant staff changes for one application.)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply