locked
Allow user to create new tables, but only read access for existing RRS feed

  • Question

  • I haven't found the exact answer through search so here's my situation (Standard SQL2005 on Server 2003).

     

    A. We have an application that populates a single database with data in several static tables.

    B. These tables should not be altered and are set as read only for Authenticated SQL users.

    C. Requirements desired are that a user have read only access for existing tables (db_datareader/deny_datawriter is the default for the database), yet create new tables and have all rights to those created.

    D. For now there's one initial user for the database, but there will probably be more in the future.

     

    Is this possible? If so how would one accomplish this. Also, with the addition of new users is there a way (schema?) that each user would have the ability to create his/her own tables within the database without having modify access to the existing pre-populated tables.

     

    It's probably simple solution, but I'm more of a Systems Admin than DBA so any help is appreciated.

     

    Thanks.

    • Edited by Yamaboy Wednesday, April 28, 2010 5:25 PM platform update
    Wednesday, April 28, 2010 5:23 PM

Answers

  • Hi

     

    This is achievable and i am explaining them with an example here . Instead of adding them to db_datareader and db_denydatawriter you can use grant select on certain objects because

    db_datareader allows them to read all tables.

     

    -- Login with sysadmin or db_owner login and perform this

     

    use leks

    go

    create table t1

    (c1 int,c2 char)

    go

    use master

    go

    CREATE LOGIN [logger] WITH PASSWORD=N'@@Y@Y@Y', DEFAULT_DATABASE=[leks], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    use leks

    go

    CREATE USER [logger] FOR LOGIN [logger] WITH DEFAULT_SCHEMA=[logger]

    GO

    GRANT select on t1 to logger

    GRANT select on t2 to logger

    GRANT select on t3 to logger

    .

    .

    GRANT select on tn to logger

    GO

    GRANT create table to logger

    GO

     

    -- Now login with logger and you will be able to read the data from tables which have to granted to select data from them

     

    select * from t1

     

    -- Try to create your own table

     

    create table loggers (c1 int)

     

    -- You will see a new table called logger.loggers in tables

     

    Hope i have given you the enough info on this , do let me know if you need more or i havent got your requirements correctly


    Thanks, Leks
    • Marked as answer by Yamaboy Thursday, April 29, 2010 5:08 PM
    Wednesday, April 28, 2010 11:47 PM