June 21, 2012 at 3:31 pm
Hi everyone
I have a DB with objects belong dbo schema and user1 schema( dbo.table1, dbo.table2, user1.table1, user1.table2, etc) , when I login with sa, of course, I can see all objects buy a need set an user with readonly privileges in that DB and it can see all objects of all schemas (dbo and user1). The readonly user needs display all object like sa but with read only privileges. How can I set a new schema to allow this?.
Thanks
June 21, 2012 at 4:59 pm
minorgm (6/21/2012)
Hi everyoneI have a DB with objects belong dbo schema and user1 schema( dbo.table1, dbo.table2, user1.table1, user1.table2, etc) , when I login with sa, of course, I can see all objects buy a need set an user with readonly privileges in that DB and it can see all objects of all schemas (dbo and user1). The readonly user needs display all object like sa but with read only privileges. How can I set a new schema to allow this?.
Thanks
To enable a user to view all object definitions, e.g. list of all tables, procs triggers and views and their definitions:
USE [YourDatabaseHere];
GRANT VIEW DEFINITION TO [TheUserName];
To enable a user to read data from all tables and views you can add them to db_datareader, but in a production setting I would only recommend allowing this for users doing research, and only temporarily. I would never recommend allowing this for an application service account. Ideally applications will use stored procedures for all data access and require no Fixed Database Role memberships.
Personally I do not like to add anyone directly to the Fixed Database Roles. But if it is necessary, I recommend creating a new User-defined Database Role, adding users to the Role, and then adding the Role to the Fixed Role. Like so:
USE [YourDatabaseHere]
GO
-- create new role for your users to belong to
CREATE ROLE [YourRoleName]
GO
-- add your Role to the Fixed Role
EXEC sys.sp_addrolemember
@rolename = N'db_datareader',
@membername = N'YourRoleName';
GO
-- add the user to your Role
EXEC sys.sp_addrolemember
@rolename = N'YourRoleName',
@membername = N'TheUserName';
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 22, 2012 at 9:54 am
Thank you very much for help...I'll try your recomendations.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply