Re: How to revoke Rights in Mixed Mode Authentication

From: Arne (Arne_at_discussions.microsoft.com)
Date: 08/18/04

  • Next message: hardrain: "What is the difference?" 
    Date: Wed, 18 Aug 2004 00:19:01 -0700

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

  • Next message: hardrain: "What is the difference?"

    Relevant Pages

    • Re: Cant get global config data and KB 900499 doesnt help
      ... The account you use to create the database MUST have the following rights on ... DECLARE @AdminVSAccount nvarchar ... DECLARE @ContentVSAccount nvarchar ... EXEC sp_grantlogin @ContentVSAccount; ...
      (microsoft.public.sharepoint.windowsservices)
    • Re: Permission in database
      ... You can create database roles and assign object permissions to these ... EXEC sp_addrole 'Administrators' ... GRANT SELECT, INSERT, UPDATE, DELETE ON Sales TO Administrators ...
      (microsoft.public.sqlserver.security)
    • Re: stored procedure
      ... Seems you can't have a variable for the database name in CREATE DATABASE. ... the whole CRE¤ATE DATABASE command in a variable and use EXECto execute the statement. ... But why do you want to create a database from inside a stored procedure? ... >> is done inside EXEC so it is in its own scope. ...
      (microsoft.public.sqlserver.server)
    • Re: Database Ownership
      ... can I declare database ownership on more than one ... believe the performance difference is noticeable for most applications. ... then grant select permissions on the views. ... EXEC sp_changedbowner 'MyLogin' ...
      (microsoft.public.sqlserver.security)
    • Re: Permissions!
      ... be aware that 'dbo' is a special user that has full permissions. ... Database permissions are not checked for the 'dbo' user so it serves no ... You will need individual logins/users so that SQL Server can identify users ... EXEC sp_addrole 'Role1' ...
      (microsoft.public.sqlserver.security)
    Loading