Re: SQL Server Express

Roger Wolter[MSFT] wrote:
To allow your SQL Server authenticated users to create tables, you have to GRANT them permissions to do so with the GRANT command. If all you want the user to do is create tables, you would specify:
GRANT CREATE DATABASE on DATABASE::xxx to SAM
If you want the user to do anything within that database you could try:
GRANT CONTROL on DATABASE::xxx to SAM

Yes, I am very familiar with configuring specific SQL Server accounts for access, and I am having no real problems in this area (now that I have figured out the SCHEMA topics in SQL Server 2005).

The Windows users have rights because they are administrators. There's not really a good way to block administrator access without making your database pretty hard to administer.

So any client application that connects to a SQL Server 2005 database, and especially to a SQL Express database, using "Integrated Security=SSPI" will automatically have full and unrestricted access to any and all databases, schemas, tables, and views that exist and can freely access, modify and delete not only data but the table definitions as well?

That seems to be an extreme security hole, if true, especially as SQL Express seems to have no mechanism for turning off Windows authentication -- can this be done with full SQL Server 2005? Are these restrictions specific to SQL Server Express or generally applicable to SQL Server 2005?

-ken
.

Relevant Pages

  • Re: Install MSDE w/ MSDE Depl.Toolkit. What permissions when using Win Auth?
    ... > have my SQL database sitting on my server. ... > the local MSDE database. ... on your SQL Server you grant login to that group and grant db access too... ...
    (microsoft.public.sqlserver.msde)
  • Re: How to Generate all grants to a user role? (SQL Server 2000)
    ... another version of the database. ... WHEN 204 THEN 'GRANT' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: user defined Role - HELP
    ... Grant ALTER TABLE on tblReportNums to 'UM Case Mgmt' ... Grant the role ALTER permission on the table. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie to security
    ... Use sp_grantdbaccess to grant access to the database. ... databases when you are new to security. ... Microsoft SQL Server 2000 SP3 Security Features and Best ...
    (microsoft.public.sqlserver.security)