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: view definition of sys.syscomments
    ... SQL Server MVP ... grant view any definition to ... The error message from auditing tool: The SELECT permission was denied on ... If I go to Logins, double click the login name, under Scurables, click Add ...
    (microsoft.public.sqlserver.security)
  • Re: Can I Install SQL Express on SBS 2008 Std.?
    ... I am able to manage the SQL instance when logged into the server itself but not remotely from the desktop. ... The error you are seeing is typical of what you would get if you had the wrong instance name OR if there were permissions issues with you connecting to it. ... If you go this approach then do create your own database and don't reuse the SBSMonitoring one. ...
    (microsoft.public.windows.server.sbs)
  • Re: Grant View Server State to Public - good idea?
    ... We are running SQL 2000 - is the script for SQL 2005? ... I would not grant it to public, that is just way too wide. ... If you search the SQL Server Books Online for "VIEW SERVER STATE" it comes ... by the VIEW SERVER STATE permission. ...
    (microsoft.public.sqlserver.security)