niladri.primalink (4/22/2013)
Hi Lowell,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?
Please help...
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
Lowell