How to revoke Rights in Mixed Mode Authentication

Hi all,
we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
Authentication.
Revoking rights to database users using SQL-Server logins does not seem to
work properly.
When I log in as TinyTim with SQL-Server authentification I can happily
create views on the database (TinyTim is assigned to), select, insert and
update in ForbiddenTable, ...
Output of sp_helpuser 'TinyTim'
UserName GroupName LoginName DefDBMame UserID
----
--
TinyTim Auswerter NULL NULL 5
Output of sp_helprotect, entries related to user TinyTim
Owner Object Grantee Granto ProtectType Action Column
----
--
dbo ForbiddenTable Auswerter dbo Deny Insert .
dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
dbo ForbiddenTable TinyTim dbo Deny Delete .
dbo ForbiddenTable TinyTim dbo Deny Insert .
dbo ForbiddenTable TinyTim dbo Deny References(All+New)
dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
. . public dbo Deny Backup Database .
. . public dbo Deny Backup Transaction .
. . public dbo Deny Create Default .
. . public dbo Deny Create Function .
. . public dbo Deny Create Procedure .
. . public dbo Deny Create Rule .
. . public dbo Deny Create Table .
. . public dbo Deny Create View .
Thanks in advance
Regards,
ArneArne,
Is TinyTim a member of the sysadmin role? You can't deny anyhing to
sysadmins.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:3DEB6B87-3D28-40D8-9262-AB527E486F47@.microsoft.com...
> Hi all,
> we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
> Authentication.
> Revoking rights to database users using SQL-Server logins does not seem to
> work properly.
> When I log in as TinyTim with SQL-Server authentification I can happily
> create views on the database (TinyTim is assigned to), select, insert and
> update in ForbiddenTable, ...
>
> Output of sp_helpuser 'TinyTim'
> UserName GroupName LoginName DefDBMame UserID
> ----
--
> TinyTim Auswerter NULL NULL 5
> Output of sp_helprotect, entries related to user TinyTim
> Owner Object Grantee Granto ProtectType Action Column
> ----
--
> dbo ForbiddenTable Auswerter dbo Deny Insert .
> dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Delete .
> dbo ForbiddenTable TinyTim dbo Deny Insert .
> dbo ForbiddenTable TinyTim dbo Deny References(All+New)
> dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
> . . public dbo Deny Backup Database .
> . . public dbo Deny Backup Transaction .
> . . public dbo Deny Create Default .
> . . public dbo Deny Create Function .
> . . public dbo Deny Create Procedure .
> . . public dbo Deny Create Rule .
> . . public dbo Deny Create Table .
> . . public dbo Deny Create View .
>
> Thanks in advance
> Regards,
> Arne
>|||"Dejan Sarka" wrote:

> Arne,
> Is TinyTim a member of the sysadmin role? You can't deny anyhing to
> sysadmins.
Hi Dejan,
promising shot but: TinyTim is not a member of any server role
Arne|||Arne,
I can't reproduce this. Can you generate T-SQL script for the table,
database users, logins and permissons? It can be done quickly in EM, just
right-click on the table and select All Tasks -> Generate SQLScripts. Then
in Options menu select optins for logins, users and permissions. Post the
script here, so we can check it.
Thanks,
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:861933B6-FB6E-44C5-8674-470764833EE7@.microsoft.com...
> "Dejan Sarka" wrote:
>
> Hi Dejan,
> promising shot but: TinyTim is not a member of any server role
> Arne|||> Arne,
> I can't reproduce this. Can you generate T-SQL script for the table,
> database users, logins and permissons? It can be done quickly in EM, just
Hi Dejan,
the generated script does not contain the settings for the 'public' role -
(just did not come out by the described procedure) the properties of the
database ProsimTest show red X's in each checkbox - hope the rest of it
helps.
In case it is relevant: we are using the standard edition of SQL Server.
Regards,
Arne
if not exists (select * from master.dbo.syslogins where loginname =
N'TinyTim')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'ProsimTest', @.loginlang = N'Deutsch'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
END
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
GO
if not exists (select * from dbo.sysusers where name = N'Any' and uid < 1638
2)
EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
GO
if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid <
16382)
EXEC sp_grantdbaccess N'TinyTim'
GO
DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
[TinyTim]
GO
if not exists (select * from dbo.sysusers where name = N'Auswerter' and uid
> 16399)
EXEC sp_addrole N'Auswerter'
GO
exec sp_addrolemember N'Auswerter', N'TinyTim'
GO
CREATE TABLE [dbo].[ForbiddenTable] (
[ID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
GO
DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswerter
] CASCADE
GO
DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
[dbo].[ForbiddenTable] TO [TinyTim] CASCADE
GO|||Arne,
I run the script on my SQL Server and I still can't reprduce the problem.
Here TinyTim is denied everything. I use SQL 2k Dev edtion with SP3a. Maybe
you should contact support?
Sorry I couldn't help more.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:81A8E360-A236-44CB-A66C-7C5E43BD2BB6@.microsoft.com...
just[vbcol=seagreen]
> Hi Dejan,
> the generated script does not contain the settings for the 'public' role -
> (just did not come out by the described procedure) the properties of the
> database ProsimTest show red X's in each checkbox - hope the rest of it
> helps.
> In case it is relevant: we are using the standard edition of SQL Server.
>
> Regards,
> Arne
>
> if not exists (select * from master.dbo.syslogins where loginname =
> N'TinyTim')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'ProsimTest', @.loginlang = N'Deutsch'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
> END
> GO
>
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
> GO
>
> if not exists (select * from dbo.sysusers where name = N'Any' and uid <
16382)
> EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
> GO
> if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid
<
> 16382)
> EXEC sp_grantdbaccess N'TinyTim'
> GO
> DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
> DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
> [TinyTim]
> GO
> if not exists (select * from dbo.sysusers where name = N'Auswerter' and
uid
> EXEC sp_addrole N'Auswerter'
> GO
> exec sp_addrolemember N'Auswerter', N'TinyTim'
> GO
> CREATE TABLE [dbo].[ForbiddenTable] (
> [ID] [int] NOT NULL ,
> [Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
> GO
> DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswert
er] CASCADE
> GO
> DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
> [dbo].[ForbiddenTable] TO [TinyTim] CASCADE
> GO
>