Re: permissions not working


Thanks for the help! When I do SELECT USER it does give me dbo. I am
not an expert with networking or with SQL server. Does this mean that
at the windows NT level they have sysadmin rights because I did not
give them these rights on the database?

It is likely that the user in question is a member of the Windows Administrators group on the SQL Server. Administrators group members access the SQL Server via membership of the BUILTIN\Administrators login are sysadmin role members by default in SQL 2000. Sysadmin role members are dbo in all databases and is why Erland suggested you check SELECT USER.

--
Hope this helps.

Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/

"JEM" <Jenn@xxxxxxxxxxxxxxxxx> wrote in message news:29eaeae8-dfd3-4ecf-8d8e-3122fb34914b@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Feb 15, 6:21 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
JEM (J...@xxxxxxxxxxxxxxxxx) writes:
> Hi. I have a SQL Server 2000 database,using Windows Authentication,
> and have created some new roles and assigned permissions to the
> various tables, views and sprocs. However, the permissions are not
> working. When I log on as a user in one of the roles, i still have
> access to insert, update, delete records in tables that i have
> specifically denied permissions on (they should only have select
> permissions which they have). I am able to log onto Query Analyzer
> and do everything with the table that i should not be able to do. The
> user is only a member of public and this role. The objects are all
> owned by dbo. Any ideas?

If you say "SELECT USER" what does it say? The name of this user or
does it by chance say "dbo"? In the latter case your login somehow
is a sysadmin member and is of course able to do anything.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

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

Thanks for the help! When I do SELECT USER it does give me dbo. I am
not an expert with networking or with SQL server. Does this mean that
at the windows NT level they have sysadmin rights because I did not
give them these rights on the database?

.

Relevant Pages

  • Re: SQL Login
    ... The sa login account is a member of that role as well. ... Dejan Sarka, SQL Server MVP ... > But how do we add the User X to the sysadmin / ...
    (microsoft.public.sqlserver.security)
  • Re: permissions not working
    ... at the windows NT level they have sysadmin rights because I did not ... It is likely that the user in question is a member of the Windows ... Administrators group on the SQL Server. ...
    (microsoft.public.sqlserver.security)
  • Re: Cant edit logins or server roles
    ... A windows local admin may have sysadmin access for a SQL Server ... don't seem to be a member of sysadmins. ...
    (microsoft.public.sqlserver.security)
  • Re: XP_CMDSHELL
    ... When master..xp_cmdshell is called by a user who is a member of the sysadmin ... fixedserver role, master..xp_cmdshell will be executed under the account's ... security context in which the SQL Server service is running. ...
    (microsoft.public.sqlserver.programming)
  • Re: permissions required for executing CDOSys stored procedures
    ... he is by default member of the sysadmin server ... role on the SQL Server database unless steps are taken to prevent that. ... sysadmin and who has not been granted specific execute permissions on the ... it is possible to GRANT EXECUTE ON sp_OACreate TO ...
    (microsoft.public.sqlserver.security)