Hi everyone, I need to revoke access to all the users for creation of tables and other table related activities. but users can be insert, update, delete and select queries on the tables.
This does not apply for stored procedures. users will be creating & modifying stored procedures.
Please give me the steps including commands scripts.
asked Nov 03 '10 at 12:49 PM in Default
Things to check to see why users have CREATE TABLE permissions:
answered Nov 04 '10 at 02:07 AM
K. Brian Kelley
Fatherjack has the right answer. If you need to change your security, you need to understand those commands and the MSDN references he provides are a very good place to start.
I will point out that if you like GUIs you can do everything through SSMS without any typing. If you go under the security heading and bring up the properties for a given log in you can add or remove users from a database and add or remove them from role membership for that database.
The other thing you may need to look at is using an execute as clause in certain procedures if the procedure will do things that require permissions the user does not have. The MSDN description for this is at: http://msdn.microsoft.com/en-us/library/ms188354.aspx and there is a good 3rd party overview at [http://www.mssqltips.com/tip.asp?tip=1227]: http://www.mssqltips.com/tip.asp?tip=1227
answered Nov 03 '10 at 02:10 PM
If it is a real emergency you can start by pulling then out of the db_owner role and only have them in the db_datareader and db_datawriter roles. And I sure hope they aren't in the server sysadmin role. Of course this is the shotgun approach with no guarantees of what it will do in your environment.
Actually, I can probably guarantee that something will stop working for someone somewhere.As Fatherjack has said... this is not something you do urgently. You can break so-o-o many things.
answered Nov 03 '10 at 01:18 PM
This shouldnt be urgent, this should be planned in advance of a system going live with real data. At that stage it isnt urgent, its part of the project.
REVOKE is detailed here: http://msdn.microsoft.com/en-us/library/ms187728.aspx
answered Nov 03 '10 at 01:08 PM