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'.
*/