Thanks for your reply I have craeted role under CAS database..then grant select option and after that when I add rolemember then it shows me error
User or role 'ap' does not exist in this database. as 'ap' does not have any access of CAS database.
Am I creating role in right place?
most likely, like ldobusiness stated, you'll need to add the login for "ap" as a user in the CAS database; THEN add the user to the role:
for my code example below, Im guessing that the login is "ap", but modify to your actual names:
Create USER [ap] FOR LOGIN [ap]
EXEC sp_addrolemember N'FiveTablesReadOnly', N'ap'
then you can test it yourself, without the user's credentials/password, since you are a sysadmin:
--change into the test user:
EXECUTE AS USER='ap';
SELECT * FROM BankOffices --should work
DELETE FROM BankOffices WHERE 1=0 --will fail due to permissions; otherwise, no rows updated, but the user has permissiosn from another source.
REVERT;--change back into superuser
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!