SQL Server - Custom Roles and Permissions

(somewhat of a newbie question)  How do I set up a custom role in SQL Server 2012 and add 2 users.  To define this further, I am looking to:
1) use SQL Server authentication (not Windows)
2) create a custom role in database myDB in Sql Server instance SQLEXPRESS
3) the custom role will have read only permissions for table tblA and view vwB
4) add 2 users (user1 and user2) under this same custom role
5) do the users need to be added to the SQLEXPRESS instance and myDB?
5) can this be done in using the SSMS GUI, and also with the query editor?
eeyoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

G GodwinDatabase AdministratorCommented:
In SSMS, connect to the instance.

Find Security and expand it.  
FInd Logins and expand it.
If the SQL logins you desire are not found, you will need to right-click on logins and select the option to add new login.  
(this assumes the instance is configured for mixed mode.)
Add the SQL accounts setting the password desired.
Locate the newly created login(s). Right-click and select properties.  Add the database you want them to have access to under user mapping.

Select the database.  
Expand security.
Right-click on database roles and add new role.  
Grant desired rights to the role.
Add members to the role.

At each step above, you can find the generate script option to see how the T-SQL can be written to do this in the query editor.

Best Regards,
GDG_DBA
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G GodwinDatabase AdministratorCommented:
Let me know if the instance is not configured for mixed-mode.  I will walk you through that change.
-GDG
0
G GodwinDatabase AdministratorCommented:
Here's the t-sql based on your post...

USE [master]
GO
CREATE LOGIN [user1] WITH PASSWORD=N'ChangeThis', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [user2] WITH PASSWORD=N'ChangeThis', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO


USE [myDB]
GO
CREATE USER [user1] FOR LOGIN [user1] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [user2] FOR LOGIN [user2] WITH DEFAULT_SCHEMA=[dbo]
GO

CREATE ROLE [myRole]
GO
ALTER ROLE [myRole] ADD MEMBER [user1]
GO
ALTER ROLE [myRole] ADD MEMBER [user2]
GO
GRANT SELECT ON [dbo].[tblA] TO [myRole]
GO
GRANT SELECT ON [dbo].[vwB] TO [myRole]
GO

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
eeyo, do you still need help with this question?
0
eeyoAuthor Commented:
GDG_DBA,
Sorry for the delay. I was away for the weekend.  Your instructions were pretty easy to follow and worked well.  Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.