Re: Complete Neophyte Question(s)

James (minorkeys@xxxxxxxxx) writes:
Disregard, seems my google-ing skills were lacking.

You should not have to go Google to find out what WITH GRANT means. SQL
Server comes with online documentation on you hard disk.

WITH GRANT is one of the more esotheric features in SQL Server in my
opinion, but maybe that says more about the simplistic security of the
system I work with.

More generic question as I'm messing with the Northwind database to get
a thorough understanding of this. If a user isn't a member of any roles
and has no permissions explicitly defined, does it err on the side of
deny or grant? Or does that depend on the permission? Right now I have
a user who has Connect as the only database level permission, no roles,
no secureables but can still view definition, it seems. I'm able to
connect and view all of the tables, although everything else seems
locked down. I have refreshed and can still see them.

If no permissions have been granted, then you have no permissions. That is,
if run the below in a database, the SELECT should not return anything:

CREATE LOGIN erik WITH PASSWORD='ärtsoppa'
go
CREATE USER erik
go
EXECUTE AS LOGIN = 'erik'
go
SELECT name FROM sys.objects
go
REVERT
go
DROP USER erik
go
DROP LOGIN erik

....unless rights have been granted to the public role.

In SQL 2005 a user only has permission to see the definition of objects
to which he been granted some access. More exactly he needs VIEW DEFINITION,
but this permission is implied if he already has SELECT permission.

Where DENY comes in is that it overrides GRANT. Say that a user is a member
of a role that has SELECT permission to a table X, but that himself he
has been denied access to the table. Then he cannot access that table.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Allowing Anonymous write access only.
    ... need at least READ permission for login. ... > been set up so that anonymous FTP users have write access only, this> may seem insecure and we do get a certain ammount of hackers or> taggers testing the system by dropping test files and folders onto the> server, but because anonymous users do not have read access they soon> find that they cannot download anything they upload and go elsewhere. ... This is where my problems have started,> I initialy replicated all the IIS setting and NTFS permission from my> NT box on my 2003 box but so far have been unable to achive the same> result, it appaers that I can only grant anonymous write access if I ...
    (microsoft.public.inetserver.iis.ftp)
  • Re: Store procedure vs Direct statement ???
    ... sql also executes on the server. ... In response to your example of stored procedures having a security advantage ... When you give users permission to access tables you must be aware you have given ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: User Permissions to Start a Job
    ... Permission on SQL can actually be refined a number of different ways. ... if your server is setup to use SQL Authentication ... you are logged in using the sa account, ...
    (microsoft.public.sqlserver.server)
  • RE: ALTER PROCEDURE permission
    ... SQL Books On-line - GRANT Object Permissions ... SQL Books On-line - ALTER PROCEDURE ... this permission when you are granted CREATE PROCEDURE permission but this ...
    (microsoft.public.sqlserver.server)
  • Re: Permission concept MSSQL05
    ... Each Server has its own security-DB where all the rolls are ... stored and their permission to each DB. ... Well, anything that worked in SQL 2000 will work in SQL 2005, but of ... there are tons of changes in the security area. ...
    (microsoft.public.sqlserver.security)