0

I am fairly new to SQL. I use microsoft sql server management studio. I used Master database. Created a server role. But when I try to add members to the role or grant it permissions it says it doesn't exist or I might not have the permissions to do that. The role is in my server object lists so it has been created.

Update : I managed to add members to my role but I still can't grant it any permissions.

I tried logging in as sa without any luck.

I'm gonna post my detailed code and context see if anything rings a bell.

2 Answers 2

0

What permissions does the account you are using have and what is the code you are using to add users to the role?

it should be

USE master
ALTER SERVER ROLE MyRole ADD MEMBER MyLogin

I would expect you have fallen foul to either

  • omitting the SERVER keyword
  • tried adding a database user rather than a server login to the role

Based on your comment below, it seems you are having issues with the GRANT of permissions to your server role and I would assume your code is something like:

CREATE SERVER ROLE MyServerRole;
GRANT SELECT, UPDATE ON MyDatabase TO MyServerRole;

This will not work as you are granting database permissions to a server principal, you will need to create a role in your database and GRANT permissions to that

USE MyDatabase
CREATE ROLE MyDatabaseRole
GRANT SELECT, UPDATE ON DATABASE:: MyDatabase TO MyDatabaseRole;
5
  • Shoot... I did both of these mistakes -.- thank you so much. I still can't grant permissions to that server role though :/ Nov 14, 2022 at 21:29
  • I wrote : use master Grant select, update On database To server_role Nov 14, 2022 at 21:29
  • At one point, I logged in as sa to see if it would change something but it didnt so unsure Nov 15, 2022 at 2:18
  • @KarianeJardin now updated
    – SE1986
    Nov 15, 2022 at 10:15
  • Thank you so much. I was following the teacher's code but I guess he didn't know or was confused. Nov 15, 2022 at 14:16
0

Can you find your created role with the following statement?

SELECT *
FROM   sys.server_principals AS sp
WHERE  type = 'R';

If you can find it, you can use the following statement to add an user to the role:

ALTER SERVER ROLE [NameOfServerRole] ADD MEMBER [NameOfUser];
3
  • Thank you, I will try that and update you! Nov 14, 2022 at 17:29
  • It did work thank you! I'm still unable to grant permission to that server role though. Nov 14, 2022 at 21:29
  • I wrote use master Grant select, update On database To server_role Nov 14, 2022 at 21:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.