SA Permissions
-
I am trying to create a user that has all sa privledges except for DELETE. I was just wondering if anyone else has came across a similar setup. These are some of the following queries I have used so far on each database.
EXEC sp_msforeachtable 'EXEC(''GRANT ALL ON ? TO superuser'')'
EXEC sp_msforeachtable 'EXEC(''REVOKE DELETE ON ? TO superuser'')'
The user is still unable to EXECUTE Stored Procedures, but I found a query online that was able to GRANT EXECUTE all sproc's to the user for each db.
Any advice on a path to take please let me know. I need to REVOKE DELETE to the user, but in retrospect, GRANT the user all permissions that an sa user would have. I understand there is a lot more permissions needed than just those, but its just a sample.
Regards,
Jarrod
-
I'm interested in seeing what others recommend, but I think there are several approaches. One approach is to give sysadmin rights at the server level and db_owner rights at the database level. Then run your REVOKE DELETE script. Of course, since that account has sysadmin rights, it can always grant itself DELETE permissions, undoing what you just did.
Perhaps you should explain the WHY of wanting to do this. That might shed some light on the best approach. Is it an account that someone will actually use on a regular basis, or is it an account that an application will use and you don't want developers to issue ad-hoc delete statements within the app?
Regards,
Rubes -
This should be what DENY is for. Because the user is a sysadmin, you shouldn't have to explicitly grant anything, but the DENY would, well, deny them the ability to delete. User rubes does bring up an interesting point though in that the user may be able to revoke the deny; it's worth testing.
-
Im glad you find this interesting. In fact, it is an account that will be used very consistently. We are currently using the sa account for all of our db operations. I can't seem to give the superuser account sysadmin privledges. I did at one point and than ran the following, but being a member of the sysadmin role, you can't revoke any permissions.
EXEC sp_msforeachtable 'EXEC(''REVOKE DELETE ON ? TO superuser'')'
I added the superuser to the following server roles:
bulkadmin
dbcreator
diskadmin
processadmin
securityadmin
serveradmin
setupadmin
I than ran the following queries on each database as well as the online script that issues GRANT EXEC on all stored procs.
EXEC sp_msforeachtable 'EXEC(''GRANT ALL ON ? TO superuser'')'
EXEC sp_msforeachtable 'EXEC(''REVOKE DELETE ON ? TO superuser'')'
This just seems like a very 'dirty' way of achieving this.
Thanks for all your replies...
Any insight is welcome
-
Some other issues you may need to address are: does revoking delete allow the user to run a truncate? If the user has all rights except delete they can still run a drop table command and accomplish a delete that way. If a person is trusted to manage security and run all the DDL commands, I would think they could be trusted to know how to avoid deleting data and/or know how to properly delete data.
What is the point of this user only having delete restricted? It seems to me that you need to re-examine what this user account really needs to be able to do and just grant the rights that are necessary, instead of granting everything and just revoking what they don't need.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply