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: 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)
  • Re: Problems with SLP
    ... SQL is set up using mixed mode, ... >I have set up an SLP in the lab, and when trying to browse to the SLP web ... > "The machine-default permission settings do not grant Local Activation ... > permission for the COM Server application with CLSID ...
    (microsoft.public.sms.setup)
  • Re: stored procedure xp_cmdshell
    ... It is generally a very bad thing to ever grant xp_cmdshell to a anyone. ... Granted you'll have to give this permission to the user ASP.NET is running ... I was originally planning to have the images to SQL, ... > xp_cmdshell stored procedure and of course the master database (which ...
    (microsoft.public.sqlserver.programming)