0

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

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.

  • Forgive my ignorance...but how exactly can I grant a SELECT on a table that doesn't exist yet. Is there some syntactical equivalent to "GRANT SELECT ON [All Tables That Might Exist] to [TestAcct]? – nil_jones_pdx May 6 '15 at 21:14
  • You need to create the table in one batch, then grant permissions to it in another batch. If necessary, you could use dynamic T-SQL to accomplish this in one piece of code. – Max Vernon May 7 '15 at 1:40
0

The only permissions you granted that user are create table and alter schema. Unless you specifically grant the permissions you want that user to have, they won't have it. Or you could grant a role.

0

A user can own a object and thus have full rights on it. But by default, when developers create objects in a schema, the objects are owned by the security principal that owns the schema, not the developer. See: ownership and user schema seperation So the User of your question cannot select from the table he created.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.