Deny the 'drop database' right to a user with dbcreator server role

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  May 22, 2009 at 2:09 am

  #130416

  Hey guys,

  Hopefully a quickie.

  I hahve a user who wants to restore databases from a specific folder.

  I've got most of it figured but want to revoke the 'dbcreator' access of allowing database deletion.

  DENY DELETE TO {user} - doesnt work.

  Advise please

  Adam Zacks-------------------------------------------Be Nice, Or Leave

• Joy Smith San

  SSC-Insane

  Points: 24877

  More actions

  May 22, 2009 at 2:12 am

  #998717

  Allocate the rights to "ROLE", not user.

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  May 22, 2009 at 2:26 am

  #998721

  Thanks for your reply.

  Can you explain what you mean?

  Adam Zacks-------------------------------------------Be Nice, Or Leave

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 22, 2009 at 7:00 am

  #998832

  something like this example might help.

  I'm creating 3 roles...one that is almost-but-not-quite-admin, one for regular users, and a third to explicity deny deletion rights.

  i then assign various users to those roles so that together, they give or take away the permissions i want them to have.

  --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 ROLE [WhateverDEVUsers]

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

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

  --assign my two admins to my more powerful role

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'

  EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'

  --assign my ordinary role to the login for network users

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

  --create the Role to prevent deletions

  CREATE ROLE [WhateverPreventDeletions]

  --prevent my almost-admins from deleting data

  DENY DELETE TO [WhateverPreventDeletions]

  EXEC sp_addrolemember N'WhateverPreventDeletions', N'bob'

  EXEC sp_addrolemember N'WhateverPreventDeletions', N'jeff'

  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!

• Schadenfreude-Mei

  SSChampion

  Points: 14083

  More actions

  May 22, 2009 at 7:40 am

  #998855

  Hey Lowell,

  Cool avatar btw. I have already tried DENY DELETE TO {user} and it doesnt work (my guess is that for some reason the DBCREATOR server role overwrites it}. Any other ideas?

  Basically all the user needs to do is restore one database withe the same backup file over and over again...

  Thought this would be simple(s)

  Adam Zacks-------------------------------------------Be Nice, Or Leave

• Lowell

  SSC Guru

  Points: 323518

  More actions

  May 22, 2009 at 7:48 am

  #998861

  doh i'm sorry; i jumped to conclusions and thought the question was "how to assign/deny permissions to a role" ; Lemme test a couple of thoeries and i'll post the results.

  I belive I have an example of a "normal" user calling a stored procedure, and the stored procedure (in master) runs with EXECUTE AS for elevated permissions to restore a database.

  I need to make sure it still works, but that was how I did it before....so the end user never had dbowner/creator rights at all.

  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!