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'
No comments:
Post a Comment