SQL Server DW Security

Normally we grant read access to DW for all users. Here is a typical script you run to allow a user to conusme the DW:

GRANT SELECT ON SCHEMA::[dbo] TO [user1]
GRANT VIEW DEFINITION ON SCHEMA::[dbo] TO [user1]
EXEC sp_addrolemember N’db_datareader’, N’user1′

Then to test it:
EXEC(‘SELECT TOP 10 * FROM table1’) AS USER=‘user1’

Leave a Reply

Your email address will not be published. Required fields are marked *