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