How to check roles for sql users

 

The following query returns the members of the database roles.

SELECT DP1.name AS DatabaseRoleName,   

   isnull (DP2.name, 'No members') AS DatabaseUserName   

 FROM sys.database_role_members AS DRM  

 RIGHT OUTER JOIN sys.database_principals AS DP1  

   ON DRM.role_principal_id = DP1.principal_id  

 LEFT OUTER JOIN sys.database_principals AS DP2  

   ON DRM.member_principal_id = DP2.principal_id  

WHERE DP1.type = 'R'

ORDER BY DP1.name;  

How to give SQL User access to execute Stored procedures and read/write access to database

Assumptions: stored procedures and tables are created under schema dbo. in case different schema, change the below code accordingly. 

 --Create Login with a strong password

--Run the below code in Master Database

CREATE LOGIN [sanjeebuser] WITH PASSWORD = 'VeryStrongPa55w0rD1!'

--run the below code in your Selected Database, (optional: run in master database as well)

CREATE USER [sanjeebuser]

 FOR LOGIN [sanjeebuser]

 WITH DEFAULT_SCHEMA =  dbo

GO

--Run the below code in your selected Database. please do not run the code in Master database.

CREATE ROLE [dbstoregeprocedureaccess] AUTHORIZATION [dbo]

GRANT EXECUTE,ALTER,DELETE,UPDATE,INSERT ON SCHEMA::dbo TO dbstoregeprocedureaccess;

GRANT CREATE PROCEDURE TO dbstoregeprocedureaccess;

EXEC sp_addrolemember N'db_datareader', N'sanjeebuser'

EXEC sp_addrolemember N'db_datawriter', N'sanjeebuser'

EXEC sp_addrolemember N'dbstoregeprocedureaccess', N'sanjeebuser'