Re: Complete Neophyte Question(s)

James (minorkeys@xxxxxxxxx) writes:
That makes complete sense to me, but somehow I'm overlooking something.

I have a user named james on a database. If I go into properties for
that user they have no owned schemas. They have no role membership
(including public, which isn't listed here for some reason). If I
right-click the database and go to properties -> Permissions, the only
permission they have is Connect, not view definition.

The only thing I can see is that there's a login of the same name at the
server level which is a member of the Server Role public, but my
understanding is that it's unrelated.

So the long and the short of it, is that this user can view definition on
this database, and I can't figure out why.

If you under database roles look at the public role, does it have any
permissions on anything?

If you run:

execute as login = 'james'
go
select * from sys.fn_my_permissions('dbo.Orders', 'object')
go
revert

What do you see?



--
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

  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Execute Persmission denied on object sp_OACreate
    ... > SQL Server is creating a job behind the scenes. ... > permissions. ... > SA account password and gaining access to the database. ... >>> How can get a user permissions to execute these stored procedures ...
    (microsoft.public.sqlserver.security)
  • Re: Newbie: I dont understand user permissions for table access
    ... > My database is remote to my workstation. ... > tables/fields WITHOUT specifying anything in the permissions dialogs? ... >> HOW are you connecting to SQL Server? ... what rights/permissions have been granted to the PUBLIC role? ...
    (microsoft.public.sqlserver.server)
  • Re: Execute Persmission denied on object sp_OACreate
    ... SQL Server is creating a job behind the scenes. ... SA account password and gaining access to the database. ... > SQL Server doesn't check permissions on indirectly referenced objects as ... > the proxy account security context for non-sysadmin users from Enterprise ...
    (microsoft.public.sqlserver.security)
  • Re: Security question ..
    ... What I want to prevent is any access to the database accept through our ... application unless you have elevated permissions. ... Authentication, if he is smart enough to create an NT Auth ODBC connection ... passes through to the database or to use SQL Server authentication. ...
    (microsoft.public.sqlserver.server)