SQL Server - Access to database as Read only

Hi All,

I need to give permissions to the users for the SQL server database. They need to connect to the server and can see all the tables and also select the data from the tables.
But they don't insert , alter or delete the data in the tables. Also , should not provide the permissions to view the Stored Procedures, Functions and Views.

Using Grant command you can set permission-

GRANT <permission> [ ,...n ]  
    TO <database_principal> [ ,...n ] [ WITH GRANT OPTION ]
    [ AS <database_principal> ]

<permission>::=  
permission | ALL [ PRIVILEGES ]

<database_principal> ::= 
        Database_user 
    | Database_role 
    | Application_role 
    | Database_user_mapped_to_Windows_User 
    | Database_user_mapped_to_Windows_Group 
    | Database_user_mapped_to_certificate 
    | Database_user_mapped_to_asymmetric_key 
    | Database_user_with_no_login  

Follow this link-

http://www.databasejournal.com/features/mssql/article.php/2246271/Managing-Users-Permissions-on-SQL-Server.htm
http://msdn.microsoft.com/en-us/library/ms178569.aspx

1. Connect to your database server.
2. Expand Security > Logins.
3. Right-click on the user who will be set as having read-only access (in this example "adventureworksro").
4. Select Properties.
5. Select User Mapping.
6. Map the login to the database they will have access to.
7. Tick the boxes for role membership next to public and db_datareader.
8. Confirm by clicking OK.

And give the Role: db_datareader : Members of the db_datareader fixed database role can read all data from all user tables

More Roles

http://msdn.microsoft.com/en-us/library/ms189121(SQL.100).aspx

hi,


Resolution: Grant rights to the administrator. Follow these steps:

Step 1: Go to Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration.

Step 2: Click on 'Add New Administrator'.

Step 3: In the 'SQL Server User Provisioning on Vista' dialog box, look out for the 'Member of the SqlServer SysAdmin role ' in the 'Available Privileges' box. Transfer it to the 'Privileges that will be granted to' box. Click Ok.
If you right click "SQL Server Management Studio" and choose "Run as Administrator" it will allow you to create new databases also and make sure that you create a "dedicated windows account" as service account and give this account "securityadmin server role" and "dbcreator server role" membership. Start menu > Right Click Computer and choose "manage" > Local Users and groups > right click users and choose new.

Note: You will be able to see 'Add New Administrator' in the Configuration tool only if you have logged in as an administrator

hope this will help you

You can issue an explicit deny or revoke on those objects



Regards