• i think it's the usual; there's no easy way, but you can use the metadata to build your commands to add everyone to a deny everything group

    something like this is what i think off of the top of my head.

    CREATE ROLE [NoAccessForYou];

    ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] TO [NoAccessForYou];

    ALTER AUTHORIZATION ON SCHEMA::[db_denydatareader] TO [NoAccessForYou];

    CREATE ROLE [OnlyReadAccessForYou];

    ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] TO [OnlyReadAccessForYou];

    ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [OnlyReadAccessForYou];

    declare @Batch varchar(max);

    SET @Batch = '';

    SELECT --@Batch = @Batch +

    'EXEC sp_addrolemember N''NoAccessForYou'', N''' + name + ''';' + CHAR(13) + CHAR(10),*

    FROM sys.database_principals WHERE type_desc IN('WINDOWS_USER','SQL_USER') AND principal_id > 4 ;

    print (@Batch);

    --exec (@@Batch);

    EXEC sp_droprolemember N'NoAccessForYou', N'TestUser';

    EXEC sp_addrolemember N'OnlyReadAccessForYou', N'TestUser';

    EXECUTE AS USER='TestUser';

    --do stuff

    --change back into superman

    REVERT;

    --clean up after ourself:

    DROP ROLE [OnlyReadAccessForYou];

    DROP ROLE [NoAccessForYou];

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!