none
permissions: SQL Server Database user

    Question

  • Dear Friends,

    I am planning to create a user , who can

    1. Create table
    2.Drop table
    3. Read/Write table
    4.Execute sp/functions/ SQL jobs and all executables
    5. Cannot drop database
    6.Cannot access other databases

    Could u please provide list of permisions needed to be Checked .

    Thanks in Advance

    Parixitsinh

    Thursday, November 29, 2012 7:09 AM

Answers

  • First part of the script:

    USE master
    GO
    CREATE DATABASE ExampleDB;
    GO
    CREATE LOGIN TestLogin WITH PASSWORD = N'SuperP@ssw0rd*';
    GO
    USE ExampleDB;
    GO
    CREATE SCHEMA ExampleSchema;
    GO
    CREATE USER TestUser FOR LOGIN TestLogin
    WITH DEFAULT_SCHEMA = ExampleSchema;
    GO
    GRANT CREATE TABLE TO TestUser;
    GRANT ALTER ON SCHEMA::ExampleSchema TO TestUser;

    Now the user can create and drop an object such as ExampleSchema.T1.
    The difficult part is granting lots of permissions to the database without granting the ability to drop the database.
    If you GRANT CONTROL ON DATABASE::ExampleDB TO TestUSER; then TestUser has all the permissions, but can also drop the database. So you have to grant all those permissions separately. I guess you probably want to grant the following:

    GRANT SELECT ON DATABASE::ExampleDB TO TestUser;
    GRANT INSERT ON DATABASE::ExampleDB TO TestUser;
    GRANT DELETE ON DATABASE::ExampleDB TO TestUser;
    GRANT UPDATE ON DATABASE::ExampleDB TO TestUser;
    GRANT EXECUTE ON DATABASE::ExampleDB TO TestUser;

    You could probably go with CONTROL DATABASE instead but create a trigger to rollback a DROP DATABASE statement. That might be a bit tricky.
    You should create a database role for the user and grant all these permissions to the role instead. That would let you add more people to this system. (But you didn't ask for that.)
    So far this user doesn't have any permissions outside of the database. So SQL Jobs are a separate issue.

     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, December 04, 2012 5:07 PM
  • Rick,
    Thanks for quick response. I am getting below error executing your query

    Msg 15118, Level 16, State 1, Line 2

    Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

    Please note i have provided password as 'test12345'. Here i want to disable password enforcement policy.

    Thanks in advance

    Parixit

    • Marked as answer by Parixit Monday, December 10, 2012 8:47 AM
    Thursday, December 06, 2012 5:35 AM
  • To answer this type of question yourself quickly, start to create a new login with SQL Server Management Studio, but instead of clicking OK to create a login with Enforce password policy unchecked, click Script instead. Then Management Studio will create a script like:

    USE [master]
    GO
    CREATE LOGIN [NewLogin] WITH PASSWORD=N'Whatever', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Parixit Monday, December 10, 2012 8:47 AM
    Thursday, December 06, 2012 6:40 PM

All replies

  • Geeeks ??
    Thursday, November 29, 2012 9:10 AM
  • Create a login. (If you are using SQL Server 2012 consider a contained database user which doesn't need a login. See http://msdn.microsoft.com/en-us/library/ff929071.aspx)

    For that login, create (map) a user in the appropriate database. Don't create a user for the login in other databases and if the guest account is disabled (the default) then they won't be able to go to other databases.

    To create a table, the user requires the CREATE TABLE permission and the ALTER SCHEMA permission for the schema that will contain the table.

    You can grant permissions to individual objects (tables, views, procedures) or to the schema that contains them (if you want them to have similar permissions to all objects in the schema), or to the whole database (if you want them to have similar permissions to all objects in the database).

    Familiarize yourself with the permission system. The easiest way to learn permissions is to use this poster of permissions at http://go.microsoft.com/fwlink/?LinkId=229142 The poster shows the permission syntax such as GRANT CREATE TABLE TO User1; and GRANT ALTER SCHEMA::dbo TO User1;


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Thursday, November 29, 2012 5:07 PM
  • Rick,

    Thanks for a quick response. Could you please provide SQL for creating user with below permissions

    1. Create table
    2.Drop table
    3. Read/Write table
    4.Execute sp/functions/ SQL jobs and all executables
    5. Cannot drop database
    6.Cannot access other databases

    I am using SQL Server 2008
    Thanks in Advance

    Parixit


    • Edited by Parixit Tuesday, December 04, 2012 6:19 AM
    Tuesday, December 04, 2012 6:04 AM
  • First part of the script:

    USE master
    GO
    CREATE DATABASE ExampleDB;
    GO
    CREATE LOGIN TestLogin WITH PASSWORD = N'SuperP@ssw0rd*';
    GO
    USE ExampleDB;
    GO
    CREATE SCHEMA ExampleSchema;
    GO
    CREATE USER TestUser FOR LOGIN TestLogin
    WITH DEFAULT_SCHEMA = ExampleSchema;
    GO
    GRANT CREATE TABLE TO TestUser;
    GRANT ALTER ON SCHEMA::ExampleSchema TO TestUser;

    Now the user can create and drop an object such as ExampleSchema.T1.
    The difficult part is granting lots of permissions to the database without granting the ability to drop the database.
    If you GRANT CONTROL ON DATABASE::ExampleDB TO TestUSER; then TestUser has all the permissions, but can also drop the database. So you have to grant all those permissions separately. I guess you probably want to grant the following:

    GRANT SELECT ON DATABASE::ExampleDB TO TestUser;
    GRANT INSERT ON DATABASE::ExampleDB TO TestUser;
    GRANT DELETE ON DATABASE::ExampleDB TO TestUser;
    GRANT UPDATE ON DATABASE::ExampleDB TO TestUser;
    GRANT EXECUTE ON DATABASE::ExampleDB TO TestUser;

    You could probably go with CONTROL DATABASE instead but create a trigger to rollback a DROP DATABASE statement. That might be a bit tricky.
    You should create a database role for the user and grant all these permissions to the role instead. That would let you add more people to this system. (But you didn't ask for that.)
    So far this user doesn't have any permissions outside of the database. So SQL Jobs are a separate issue.

     


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, December 04, 2012 5:07 PM
  • Rick,
    Thanks for quick response. I am getting below error executing your query

    Msg 15118, Level 16, State 1, Line 2

    Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

    Please note i have provided password as 'test12345'. Here i want to disable password enforcement policy.

    Thanks in advance

    Parixit

    • Marked as answer by Parixit Monday, December 10, 2012 8:47 AM
    Thursday, December 06, 2012 5:35 AM
  • To answer this type of question yourself quickly, start to create a new login with SQL Server Management Studio, but instead of clicking OK to create a login with Enforce password policy unchecked, click Script instead. Then Management Studio will create a script like:

    USE [master]
    GO
    CREATE LOGIN [NewLogin] WITH PASSWORD=N'Whatever', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by Parixit Monday, December 10, 2012 8:47 AM
    Thursday, December 06, 2012 6:40 PM
  • Hi Rick Byham,

    Adding one more thing to this.

    The user should also be able to Create / Modify / Execute SQL Jobs.

    By applying above setting my user is not able to see JOBs .  Could u please provide Scripts for this .

    Please do the needful.

    Thanks in Advance!

    Parixit

    Monday, January 07, 2013 6:28 AM