Sql-server – SQL Server user cannot select from a table it just created

permissionsselectsql server

I'm having trouble understanding permissions in SQL Server (in this case SQL Server 2012, but not sure how version-dependent this issue is). I can create a database, create a login/user on master, create a user for that login on the new database, grant 'CREATE TABLE' and 'ALTER' on the [dbo] schema in that new database, and then (as the new user) create the table but not select from it.

Is there some different permission necessary to SELECT from tables I just created? The end game would be to be able to GRANT various permissions on that table to [public] but if I can't SELECT from it then I'd imagine I can't do anything else on it? (Code follows)

--logged in as 'sa'
    USE [master]
    GO

    CREATE DATABASE [ThisTestDB]

    CREATE LOGIN [TestAcct]
        WITH PASSWORD = 'Passw0rd',
        DEFAULT_DATABASE = [ThisTestDB]

    CREATE USER [TestAcct]
        FOR LOGIN [TestAcct]
    GO

    USE [ThisTestDB]
    GO

    CREATE USER [TestAcct]
        FOR LOGIN [TestAcct]
    GO
--executed all lines above: Command(s) completed successfully

--still logged in as 'sa'
    USE [ThisTestDB]
    GO

    GRANT CREATE TABLE TO [TestAcct]
    GO

    GRANT ALTER ON SCHEMA :: [dbo] TO [TestAcct]
    GO
--execute the above lines: Command(s) completed successfully

--now, logged in as 'TestAcct'
    USE [ThisTestDB]
    GO

    CREATE TABLE tThisTable (
        id      INT IDENTITY(1,1),
        ts      DATETIME,
        Data    VARCHAR(1000))
--execute the above lines: Command(s) completed successfully

--still logged in as 'TestAcct'
    SELECT  * 
    FROM    tThisTable
--executed the above, error thrown:
/*
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'tThisTable', database 'ThisTestDB', schema 'dbo'.
*/

Best Answer

Granting schema modification is not, and should not be, the same as granting DML rights. Grant the user the exact rights that user needs, including 'SELECT', etc, as necessary.

See https://msdn.microsoft.com/en-us/library/ms178569.aspx for details.