Re: User permissions.

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 08/01/03 

Date: Fri, 1 Aug 2003 08:02:09 -0500

> I want to review the permissions on user basis-for example which
tables is
> user C able to access.

EXEC sp_helprotect @username = 'UserC'

> My second question - does "deny" have no effect if a user is a member
of the
> System Admins Server Role ?

You are correct that DENY has no effect on sysadmin role members because
these users are automatically aliased to dbo in all databases on the
server and the dbo user has full permissions the database..

Also note that the db_owner fixed database role is very a powerful one.
db_owner role members can manipulate database security and can grant
themselves permissions on objects even if they were previously denied.
You shouldn't routinely add users to this role. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Sezgin Rafet" <srafet@hotmail.com> wrote in message
news:OsGylDAWDHA.484@TK2MSFTNGP09.phx.gbl...
> I have standart users connecting to SQL Server and want to aplly
> restrictrictions to their database objects access. Let's say we have
users
> A,B and C and 100 tables -T1 through T100 in database  D1. I give all
the
> users access to DB1 and make them members of the public and db_owner
> Database Roles with no Server Roles membership. Then I run "deny all
on
> table_name to user_name" and repeat for all tables and users. 2 weeks
later
> I want to review the permissions on user basis-for example which
tables is
> user C able to access. What is the best way to do that  ?
>
> My second question - does "deny" have no effect if a user is a member
of the
> System Admins Server Role ?
>
>

Relevant Pages

  • Re: securityadmin
    ... add the database user to db_datareader, ... fixed server role Security Administrators. ... I can go ahead and add that login as a database user and add them ...
    (microsoft.public.sqlserver.security)
  • Re: Setup login account for application developer to access sql server
    ... You wouldn't want sa - members of sysadmin can perform any ... any activity in any database. ... >Is it i need to give sa server role and then db owner db roles to the db to ... >or i just need to give db owner db roles to the user but no need to give any ...
    (microsoft.public.sqlserver.security)
  • Re: server roles for backup
    ... No the "diskadmin" server role does not give logins access to perform ... operators access to perform backups you can give them access to the ... > tapeadmin user the database server role of disk administrators give the ...
    (microsoft.public.sqlserver.server)
  • Re: Error executing Backup
    ... For RESTORE you cannot use db_backup operator if the database doesn't exist, ... dbcreator server role should do. ... I've configure "HP Omniback" to perform backups on ... configured this account i assign this account the "System ...
    (microsoft.public.sqlserver.server)
  • Cant Deny Delete on a table.
    ... Are you users member of server role like sa ... Because normally if you deny at one place ... >I've tried denying the permission to the public role both ...
    (microsoft.public.sqlserver.programming)