Very Urgent Help Needed in SQL Security

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.


more ▼

asked Nov 03, 2010 at 12:49 PM in Default

vijay 2 gravatar image

vijay 2
236 21 21 23

(comments are locked)
10|1200 characters needed characters left

6 answers: sort newest

Things to check to see why users have CREATE TABLE permissions:

  • Are users a member of the sysadmin server role? If so, this has to be undone. Can you undo it safely?
  • Are users a member of the db_owner database role? If so, this has to be undone. Again, can you undo it safely?
  • Are users a member of the db_ddladmin database role? If so, this has to be undone. Usually this is safe to undo.
  • Do the users have explicit GRANT CREATE TABLE rights in the database? If so, this has to be undone.
  • Are the users a member of a Windows group which is a user in the database? If so, you need to check that Windows group for all of what I've mentioned above.
If you want to grant permissions automatically, this isn't exactly possible without putting in a DDL trigger or the like (I'm assuming SQL Server 2005 and higher. If you're on 2000, there's no automatic way at all). However, a better thing to do is go review Books Online for securables and scopes. If you grant the permission at a scope level, then any object contained by the scope to which the permission applies the user/role will have the permission implicitly. For instance, if I grant execute on the dbo schema, than any stored procedures in the dbo schema I'll have implicit permissions to execute.
more ▼

answered Nov 04, 2010 at 02:07 AM

K. Brian Kelley gravatar image

K. Brian Kelley
933 2

(comments are locked)
10|1200 characters needed characters left

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][2]

[2]: http://www.mssqltips.com/tip.asp?tip=1227
more ▼

answered Nov 03, 2010 at 02:10 PM

TimothyAWiseman gravatar image

15.6k 20 23 32

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Nov 03, 2010 at 01:18 PM

Blackhawk-17 gravatar image

11.9k 28 31 37

+1 - so many bad things can happen so fast its scary.
Nov 03, 2010 at 01:21 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

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
GRANT is detailed here: http://msdn.microsoft.com/en-us/library/ms187965.aspx
DENY is detailed here:http://msdn.microsoft.com/en-us/library/ms188338.aspx

you need to sprinkle small amounts of these over the users and roles in your database.
more ▼

answered Nov 03, 2010 at 01:08 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

hi thanks,

I am looking for a commant which gives all permissions to the all users for stored procedures.

Please respond.
Nov 03, 2010 at 01:13 PM vijay 2
so, giving permission is done with GRANT, check that bit out first. I am not being awkward here. You need to understand what you are doing so that you can choose to do it and know what is needed to undo it in case it goes wrong. We cant wave a magic TSQL command over the internet and fix your security.
Nov 03, 2010 at 01:20 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Nov 03, 2010 at 12:49 PM

Seen: 1006 times

Last Updated: Nov 03, 2010 at 12:49 PM