none
sql server 2008 permissions

    Question

  • I have a question about setting up permissions on a sql server 2008 r2 datbase.

     I basically wrote and enhanced some C# 2008 and C# 2010 console applications that connected to my test  sql server 2008 r2 database. On my test database, I must all full right my default.

      My 3 applications were deployed to a user acceptance testing environment that includes a user acceptance sql server 2008 r2 standard database.
      The user accpetance database was set up by the network administrator at my small company. He is the only one at my small company that knows a little bit about the dba roles and has locked down permissions.

      Due to the facts above, I would like to know what should I be aware that may need to have permissions setup for. My questions includes the followinng:
    1. When to decide if role(s) need to be setup and how to setup the roles. When I ran my applications on my test database, I had the integrated security set to true. I did not need to supply the user name and password in the connection strings to the database. My user account was setup to have a role in the database.
    Due to what I just said, will I need to have setup roles for the console applications to run on their own? If so, What kind of roles need to be setup and how do you setup these roles?
    2. I setup 3 new tables that are under the dbo schema. Do I need to have permissions setup so people and/or roles can have read, write, update and/or execute permissions? If so, how do you setup these permissions?
    3. I have also created 2 stored procedures that are used to access the 3 new tables that I setup. Thus do these stored procedures need to have read, write, update and/or execute permissions on them setup?
    4. Do statistics (explain plans) need to be run on this database that has hardly evern been used before? If so, how do you accomplisth this goal?
    5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?

    If you can any part of my questions above, I would appreciate hearing what  your answer is also.

    Thursday, October 18, 2012 4:55 AM

Answers

  • 1) It depends on the logic  you put behind, to setup roles

     EXEC sp_addlogin @loginame = @login, @passwd =@pass, @defdb = @db
            EXEC sp_addsrvrolemember @loginame=@login,@rolename = 'sysadmin'

    EXEC sp_addrolemember 'db_owner',@login

    2) By default noone have permissions on the tables. You mist grant appropriate permissions

    GRANT EXEC ON some_sp TO user-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user -- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.


    To grant all "normal" permissions on a table

       GRANT SELECT, UPDATE, DELETE, INSERT on pec TO user

    To grant user access on tables in a schema:

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO user

    To grant him access on all tables:

       GRANT SELECT, UPDATE, DELETE, INSERT TO user

    3) Grant then an appropriate permissions on set up (see above)

    4) Yes 

    DECLARE @tablename varchar(80),@shemaname varchar(80)
    DECLARE @SQL AS NVARCHAR(200)
    DECLARE TblName_cursor CURSOR FOR
    SELECT t.name,s.name FROM sys.tables t join sys.schemas s
    on s.schema_id=t.schema_id


    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor
    INTO @tablename,@shemaname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

    EXEC sp_executesql @statement = @SQL

       FETCH NEXT FROM TblName_cursor
       INTO @tablename,@shemaname
    END

    CLOSE TblName_cursor
    DEALLOCATE TblName_cursor

    5) Index Maintenance is important, see Ola's script  http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Backup databases and etc.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, October 18, 2012 7:55 AM
  • I'll give you a fairly generic answer, so please feel free to validate this with your administrator before you proceed.

    1. If you aren't going to have more than a few (say, not more than 10) users, then you probably don't need to worry about creating roles. It is probably just as effective and efficient to add users with permissions as required. If you are going to continue to run trusted connections, then those user accounts will be the domain accounts for each person that is using the application. I'd consider using an application-specific user name and password to connect to SQL Server if I have more than 10 different users or so.

    2. If you are passing all data access through stored procedures, then you don't have to grant select/insert/update/delete permissions to the underlying tables.

    3. The user account(s) that are used by the application should be granted EXECUTE permissions to the stored procedures.

    4. You only need to use the explain plans if you are having performance issues that you want to troubleshoot. If you are talking about updating statistics, then you probably just set the database configuration to auto-update statistics. We schedule statistic updates as part of our maintenance plan when we have 10's of thousands of insert/update/delete transactions or more on a daily basis.

    5. Probably so, but I don't know you're environment, so I can't help you with this one!

    Thursday, October 18, 2012 5:51 PM
  • 1) The advantage with using roles is that you can grant the permissions to the roles once, and then add users as they come and go.

    2) If the application access the tables directly, or your stored procedures users dynamic SQL, user roles will need permissions on the table directly. If the only direct access is SELECT, while all updates are through stored procedures, user roles only need SELECT access to the table.

    3) You don't grant permissions to stored procedure. But as long as a procedure and a table is ownen by the same user, permission checks for SELECT, INSERT, UPDATE and DELETE are waived. That is, if users run the procedure, they can update even if they don't have permissions on their own. But the users (or rather a role they belong to) needs EXECUTE permission on the tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 18, 2012 9:46 PM

All replies

  • I have a question about setting up permissions on a sql server 2008 r2 datbase.

     I basically wrote and enhanced some C# 2008 and C# 2010 console applications that connected to my test  sql server 2008 r2 database. On my test database, I must all full right my default.

      My 3 applications were deployed to a user acceptance testing environment that includes a user acceptance sql server 2008 r2 standard database.
      The user accpetance database was set up by the network administrator at my small company. He is the only one at my small company that knows a little bit about the dba roles and has locked down permissions.

      Due to the facts above, I would like to know what should I be aware that may need to have permissions setup for. My questions includes the followinng:
    1. When to decide if role(s) need to be setup and how to setup the roles. When I ran my applications on my test database, I had the integrated security set to true. I did not need to supply the user name and password in the connection strings to the database. My user account was setup to have a role in the database.
    Due to what I just said, will I need to have setup roles for the console applications to run on their own? If so, What kind of roles need to be setup and how do you setup these roles?
    2. I setup 3 new tables that are under the dbo schema. Do I need to have permissions setup so people and/or roles can have read, write, update and/or execute permissions? If so, how do you setup these permissions?
    3. I have also created 2 stored procedures that are used to access the 3 new tables that I setup. Thus do these stored procedures need to have read, write, update and/or execute permissions on them setup?
    4. Do statistics (explain plans) need to be run on this database that has hardly evern been used before? If so, how do you accomplisth this goal?
    5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?

    If you can any part of my questions above, I would appreciate hearing what  your answer is also.

    • Moved by CoolDadTxMVP Thursday, October 18, 2012 2:11 PM SQL related (From:Visual C# IDE)
    • Merged by Iric WenModerator Monday, October 22, 2012 1:41 AM security issue
    Thursday, October 18, 2012 4:51 AM
  • I placed this question also in the sql server  security forum since I do not know what is the best forum to place this question in.

    I have a question about setting up permissions on a sql server 2008 r2 datbase.

     I basically wrote and enhanced some C# 2008 and C# 2010 console applications that connected to my test  sql server 2008 r2 database. On my test database, I must all full right my default.

      My 3 applications were deployed to a user acceptance testing environment that includes a user acceptance sql server 2008 r2 standard database.
      The user accpetance database was set up by the network administrator at my small company. He is the only one at my small company that knows a little bit about the dba roles and has locked down permissions.

      Due to the facts above, I would like to know what should I be aware that may need to have permissions setup for. My questions includes the followinng:
    1. When to decide if role(s) need to be setup and how to setup the roles. When I ran my applications on my test database, I had the integrated security set to true. I did not need to supply the user name and password in the connection strings to the database. My user account was setup to have a role in the database.
    Due to what I just said, will I need to have setup roles for the console applications to run on their own? If so, What kind of roles need to be setup and how do you setup these roles?
    2. I setup 3 new tables that are under the dbo schema. Do I need to have permissions setup so people and/or roles can have read, write, update and/or execute permissions? If so, how do you setup these permissions?
    3. I have also created 2 stored procedures that are used to access the 3 new tables that I setup. Thus do these stored procedures need to have read, write, update and/or execute permissions on them setup?
    4. Do statistics (explain plans) need to be run on this database that has hardly evern been used before? If so, how do you accomplisth this goal?
    5. Are there other items I need to consider? If so, what are the items and what do I do to resolve those issues?

    If you can any part of my questions above, I would appreciate hearing what  your answer is also.

    Thursday, October 18, 2012 4:58 AM
  • 1) It depends on the logic  you put behind, to setup roles

     EXEC sp_addlogin @loginame = @login, @passwd =@pass, @defdb = @db
            EXEC sp_addsrvrolemember @loginame=@login,@rolename = 'sysadmin'

    EXEC sp_addrolemember 'db_owner',@login

    2) By default noone have permissions on the tables. You mist grant appropriate permissions

    GRANT EXEC ON some_sp TO user-- Grant permission on a single procedure.
    GRANT EXEC ON SCHEMA::dbo TO user -- Grant perpmission on all procedures in
                                          the dbo schema
    GRANT EXEC TO putte -- Grant EXEC permission all procedures in the database.


    To grant all "normal" permissions on a table

       GRANT SELECT, UPDATE, DELETE, INSERT on pec TO user

    To grant user access on tables in a schema:

       GRANT SELECT, UPDATE, DELETE, INSERT on SCHEMA::dbo TO user

    To grant him access on all tables:

       GRANT SELECT, UPDATE, DELETE, INSERT TO user

    3) Grant then an appropriate permissions on set up (see above)

    4) Yes 

    DECLARE @tablename varchar(80),@shemaname varchar(80)
    DECLARE @SQL AS NVARCHAR(200)
    DECLARE TblName_cursor CURSOR FOR
    SELECT t.name,s.name FROM sys.tables t join sys.schemas s
    on s.schema_id=t.schema_id


    OPEN TblName_cursor

    FETCH NEXT FROM TblName_cursor
    INTO @tablename,@shemaname

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = 'UPDATE STATISTICS '+@shemaname+'.[' + @TableName + '] WITH FULLSCAN ' ---+ CONVERT(varchar(3), @sample) + ' PERCENT'

    EXEC sp_executesql @statement = @SQL

       FETCH NEXT FROM TblName_cursor
       INTO @tablename,@shemaname
    END

    CLOSE TblName_cursor
    DEALLOCATE TblName_cursor

    5) Index Maintenance is important, see Ola's script  http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Backup databases and etc.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Thursday, October 18, 2012 7:55 AM
  • Thanks so far!

    Am I concerned about roles if so? What do I need to be aware to grant roles to users? is this related to how they have access on the database?

    Thursday, October 18, 2012 1:41 PM
  • I'll give you a fairly generic answer, so please feel free to validate this with your administrator before you proceed.

    1. If you aren't going to have more than a few (say, not more than 10) users, then you probably don't need to worry about creating roles. It is probably just as effective and efficient to add users with permissions as required. If you are going to continue to run trusted connections, then those user accounts will be the domain accounts for each person that is using the application. I'd consider using an application-specific user name and password to connect to SQL Server if I have more than 10 different users or so.

    2. If you are passing all data access through stored procedures, then you don't have to grant select/insert/update/delete permissions to the underlying tables.

    3. The user account(s) that are used by the application should be granted EXECUTE permissions to the stored procedures.

    4. You only need to use the explain plans if you are having performance issues that you want to troubleshoot. If you are talking about updating statistics, then you probably just set the database configuration to auto-update statistics. We schedule statistic updates as part of our maintenance plan when we have 10's of thousands of insert/update/delete transactions or more on a daily basis.

    5. Probably so, but I don't know you're environment, so I can't help you with this one!

    Thursday, October 18, 2012 5:51 PM
  • 1) The advantage with using roles is that you can grant the permissions to the roles once, and then add users as they come and go.

    2) If the application access the tables directly, or your stored procedures users dynamic SQL, user roles will need permissions on the table directly. If the only direct access is SELECT, while all updates are through stored procedures, user roles only need SELECT access to the table.

    3) You don't grant permissions to stored procedure. But as long as a procedure and a table is ownen by the same user, permission checks for SELECT, INSERT, UPDATE and DELETE are waived. That is, if users run the procedure, they can update even if they don't have permissions on their own. But the users (or rather a role they belong to) needs EXECUTE permission on the tables.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, October 18, 2012 9:46 PM
  • as a best practice:
    1.grant permissions on Schemas rather than on single objects
    2.only grant permissions to roles


    to accomplish that:

    1)
    1.CREATE SCHEMA SomeNameThatMakesSenseForTheObjectsContained
    2.Create Objects in that Schema or Transfer existing ones (see ALTER SCHEMA .. TRANSFER in BOL) 

    2)
    1.create appropriate role(s) (see the code from Uri Dimant)
    2.put users as members into that role
    3.grant permissions to that role on the (whole) Schema


    so in the End you would have:

    Login - User - Role- Permissions(- on Schema)


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: http://www.vb-magazin.de/forums/blogs/andreaswolter/

    Thursday, October 18, 2012 9:57 PM