What database role should I grant?

• gary1

  SSC-Insane

  Points: 21152

  More actions

  June 12, 2012 at 12:03 pm

  #259239

  Hi,

  I need to grant the below permissions to a user called Dtadmin on MYDB database:

  The login used for connecting to database should have SELECT, INSERT, UPDATE, DELETE permissions on all tables and Execute permission on the Stored Procedures

  What database role should I grant?

• Lowell

  SSC Guru

  Points: 323546

  More actions

  June 12, 2012 at 12:42 pm

  #1499983

  i would create a role like this example, and then add your user dtAdmin to that role:

  Create LOGIN [dtAdmin] WITH PASSWORD='NotTheRealPassword'

  USE [WHATEVER]

  Create USER [dtAdmin] FOR LOGIN [dtAdmin]

  CREATE ROLE [ClassicReadWriteExecute]

  EXEC sp_addrolemember N'db_datareader', N'ClassicReadWriteExecute'

  EXEC sp_addrolemember N'db_datawriter', N'ClassicReadWriteExecute'

  --can the users EXECUTE procedures? comment out if false

  GRANT EXECUTE TO [ClassicReadWriteExecute]

  --finally add our user to the role:

  EXEC sp_addrolemember N'ClassicReadWriteExecute', N'dtAdmin'

  --test:

  EXECUTE AS USER='dtAdmin'

  --who am i?

  select suser_name()

  --do stuff

  --change back into superman

  REVERT;

  --clean up after ourselves

  /*

  DROP ROLE [ClassicReadWriteExecute]

  DROP USER [dtAdmin]

  DROP LOGIN [dtAdmin]

  */

  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!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 12, 2012 at 12:59 pm

  #1499991

  Lowell (6/12/2012)

  ---

  i would create a role like this example, and then add your user dtAdmin to that role:

  Create LOGIN [dtAdmin] WITH PASSWORD='NotTheRealPassword'

  USE [WHATEVER]

  Create USER [dtAdmin] FOR LOGIN [dtAdmin]

  CREATE ROLE [ClassicReadWriteExecute]

  EXEC sp_addrolemember N'db_datareader', N'ClassicReadWriteExecute'

  EXEC sp_addrolemember N'db_datawriter', N'ClassicReadWriteExecute'

  --can the users EXECUTE procedures? comment out if false

  GRANT EXECUTE TO [ClassicReadWriteExecute]

  --finally add our user to the role:

  EXEC sp_addrolemember N'ClassicReadWriteExecute', N'dtAdmin'

  --test:

  EXECUTE AS USER='dtAdmin'

  --who am i?

  select suser_name()

  --do stuff

  --change back into superman

  REVERT;

  --clean up after ourselves

  /*

  DROP ROLE [ClassicReadWriteExecute]

  DROP USER [dtAdmin]

  DROP LOGIN [dtAdmin]

  */

  Just an FYI, db_datareader and db_datawriter don't give users execute privledges on stored procedures.

• Lowell

  SSC Guru

  Points: 323546

  More actions

  June 12, 2012 at 1:49 pm

  #1500026

  yeah i had added a seperate line "GRANT EXECUTE TO [ClassicReadWriteExecute]"

  to cover the execute permissions as well.

  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!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 12, 2012 at 1:51 pm

  #1500028

  Lowell (6/12/2012)

  ---

  yeah i had added a seperate line "GRANT EXECUTE TO [ClassicReadWriteExecute]"

  to cover the execute permissions as well.

  Okay, blind. Didn't see that the first time I read your post. I apologize.