Re: Deny Create Database

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 02/12/04 

Date: Thu, 12 Feb 2004 13:33:00 -0500

Looks like you need to re-think your strategy. If you're in db_creator, you
can create a database. If you want to ensure they can't do it - keep them
out. A sysadmin role member can do anything, so a DENY isn't going to work
here. It's best to look at what they really need and give them only those
permissions or roles. That way, you won't need to use DENY. 

--
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@cox.net> wrote in message
news:3E662297-270F-4F7B-AE8A-D0E9215E9E83@microsoft.com...
Works great, but one thing I noticed:
Even though the commands take fine and the   syspermission table  record
count changes as you would expect (master file), the command will not turn
off create database for a user in the sysadmin role or for a user who is not
in the sysadmin role but who is in the db_creator role.
So it doesn't seem to work at all.  I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually
works where it would make sense.  It makes sense that you might want to turn
it off for an administrator.  Can't get it to work.

Relevant Pages

  • Re: security of mssql database?
    ... You can't deny a SYSADMIN from accessing your database. ... ensure that only the right people have the SYSADMIN role. ... To secure your database through your application, ...
    (comp.databases.ms-sqlserver)
  • Re: Access 2007 and ADP to SQL Server 2005 in Vista
    ... If you try to connect to the server as "SA" or a different login with ... sysadmin role as Sylvain mentioned, ... If you create a new database other than connect to a existing database, ...
    (microsoft.public.access.adp.sqlserver)
  • deny permissions issues
    ... database. ... I have tried using the following commands without any success: ... DENY SELECT ON SCHEMA::sys TO public ...
    (microsoft.public.sqlserver.security)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)
  • RE: copy permissions from one user to another?
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- ADD USER TO SERVER ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- SET COMMAND TO FIND USER PERMISSIONS HAS IN CURRENT DATABASE ...
    (microsoft.public.sqlserver.security)
Quantcast