none
inheriting permissions from database -> tables?

    Question

  • Apologies if this is not the correct forum.

    I need to conduct some basic user rights administration and it is the first time i've looked at this.

    I 'adminisiter' an instance of SQL Server (in that I am the the owner, although i do not have local administrative privilages on my network, so lack those rights). I have Server permissions to create databases, which I have done, and called 'testdb'.

    I have another user, user\chuck.e.cheese, and I want this user to have read/write access to all the tables in the new database 'testdb'.

    So first i create a database user for him:

    use testdb
    exec sp_grantaccess 'user\chuck.e.cheese'

    great, now he can see the database (and all the sys tables, for some reason) when he connects via access, for example. However, I am having to explicitily add db_datawriter role access for each table i create, otherwise he can't see it.

    exec sp_addrolemember db_datawriter, user\chuck.e.cheese

    Why, if I grant access to the database for chuck, does this not mean that access is inherited by subsquently created tables in that db?

    This is very nooby, and i apologise, but it's not really my area and I just need to get the guy up and running on the database.

    best wishes


    Dave

    Friday, September 06, 2013 10:48 AM

Answers

  • 1) Any user can see the system views (not system tables in a user database) – that does no harm, as he still can only see the data, he is allowed to

    2) Instead of sp_grantdbaccess, which is deprecated, the following command should be used

    CREATE USER [user\chuck.e.cheese] FOR LOGIN [user\chuck.e.cheese];
    GO
    

    No big deal – you just now have a schema with the name of the user as well

    3) Having just access to a database, does not mean, the user OWNS the DB, right? Therefore, he has to explicitly be granted the permissions to be able to do what he is supposed to be doing.

    In your example you only want read/write access to existing tables. If he already had all permissions, he would also have the permission to create or drop tables, etc etc

    Therefore all you have to grant him is what you said:

    ALTER ROLE [db_datareader] ADD MEMBER [user\chuck.e.cheese]
    GO
    ALTER ROLE [db_datawriter] ADD MEMBER [user\chuck.e.cheese]
    GO
    

    As one security principle states: only grant the necessary permissions, nothing more.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Friday, September 06, 2013 11:05 AM

All replies

  • 1) Any user can see the system views (not system tables in a user database) – that does no harm, as he still can only see the data, he is allowed to

    2) Instead of sp_grantdbaccess, which is deprecated, the following command should be used

    CREATE USER [user\chuck.e.cheese] FOR LOGIN [user\chuck.e.cheese];
    GO
    

    No big deal – you just now have a schema with the name of the user as well

    3) Having just access to a database, does not mean, the user OWNS the DB, right? Therefore, he has to explicitly be granted the permissions to be able to do what he is supposed to be doing.

    In your example you only want read/write access to existing tables. If he already had all permissions, he would also have the permission to create or drop tables, etc etc

    Therefore all you have to grant him is what you said:

    ALTER ROLE [db_datareader] ADD MEMBER [user\chuck.e.cheese]
    GO
    ALTER ROLE [db_datawriter] ADD MEMBER [user\chuck.e.cheese]
    GO
    

    As one security principle states: only grant the necessary permissions, nothing more.


    Andreas Wolter | Microsoft Certified Master SQL Server
    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com

    Friday, September 06, 2013 11:05 AM
  • (and all the sys tables, for some reason)

    Hello Dave,

    As Andreas already wrote, it's the normal behaviour that all users can "see" and also query the system views, but the views will return only data where the user has permissions for.

    E.g. if a user queries sys.tables (Transact-SQL) he will get only those tables where he has permissions for; see also Metadata Visibility Configuration


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, September 06, 2013 11:21 AM
  • thank you both for a consise, considerately dumbed-down answer !!!

    Honestly the scope of SQL Server is insane. I spend most of my time in SSMS / SSIS and it reminds me how i am only scratching the surface.

    anyway thanks again

    dave

    Friday, September 06, 2013 2:07 PM