Dabase User Defined Role

• Chandhini

  Ten Centuries

  Points: 1243

  More actions

  September 1, 2009 at 6:39 am

  #385293

  Hi,

  In my Database, i need to create two different roles to access the db. one is for application and the other one is for development.

  For application - only execute and select permissions should be given.

  For development Team - insert, update, delete, alter, execute permissions should be given only at the stored procedures, and functions level. (users to be configured to this role).

  Database has separate schemas for tables, SPs, Functions, and Views.

  Please suggest me, how it should be done.

  Thanks in advance.

• Lowell

  SSC Guru

  Points: 323482

  More actions

  September 1, 2009 at 7:56 am

  #1047534

  Chandhini (9/1/2009)

  ---

  Hi,

  In my Database, i need to create two different roles to access the db. one is for application and the other one is for development.

  For application - only execute and select permissions should be given.

  For development Team - insert, update, delete, alter, execute permissions should be given only at the stored procedures, and functions level. (users to be configured to this role).

  Database has separate schemas for tables, SPs, Functions, and Views.

  Please suggest me, how it should be done.

  Thanks in advance.

  here's an example i like to use; it's creating two roles, one for normal users, and one for develoeprs, but they are still not db_owners.

  after the roles are created, then i add users to those roles...a couple of SLq logins as an example, and then a windows role for my regular users.

  hope this example helps.

  create database Whatever

  GO

  USE Whatever

  --create the Role for my Dev guys

  CREATE ROLE [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

  --create role for my normal users

  --create the Role for my Dev guys

  CREATE ROLE [WhateverDEVUsers]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]

  --now add specific users to nearly-Admins

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  --add this user to permit read and write

  END

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  END

  USE [WHATEVER]

  --make a user in the db for the matching login

  CREATE USER [bob] FOR LOGIN [bob]

  CREATE USER [jeff] FOR LOGIN [jeff]

  --add these logs to the role

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'

  CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]

  CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]

  EXEC sp_addrolemember N'WhateverDEVUsers', N'NT AUTHORITY\Authenticated Users'

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Chandhini

  Ten Centuries

  Points: 1243

  More actions

  September 1, 2009 at 8:28 am

  #1047568

  Thanks a lot Lowell

  Let me try this .