-1

I have an azure SQL Database. I already created a new login and user on the database. Question is i want to assign permission on the said user. Permission that will restrict the user to drop table, update data, truncate and create another role?

  • What do you want the user to have permissions for should be the first thing? and then the restriction. If just the read permissions than db_datareader is enough. – Ramakant Dadhichi Jun 19 at 11:14
  • user cannot drop tables, alter, create/alter table, create/alter sp. user can only select data. is these possible on azure sql database? – Nathan Jun 19 at 11:16
  • Yes just grant db_datareader. db_datareader Members of the db_datareader fixed database role can read all data from all user tables. – Ramakant Dadhichi Jun 19 at 12:24
  • Currently i am encountering an issue saying that : The server principal "Test" is not able to access the database "master" under the current security context. Cannot open user default database. Login failed. Login failed for user 'Test'. (Microsoft SQL Server, Error: 916) – Nathan Jun 19 at 13:23
  • Go to the OPTIONS tab --> connection properties --> Connect to Database Provide the name of the database. stackoverflow.com/questions/19094970/… – Ramakant Dadhichi Jun 19 at 14:16
0

I do not have an azure database server right now to check, but looking at the regular SQL server instance it is pretty easy. I hope, or think it is the same for an azure DB If you have created the user already, then you can map the user to the database.

CREATE USER [User] FOR LOGIN [user]

It will get public access automatically, meaning it can connect to the database. With this public access not much can be done, but to give extra Easy way to grant only read access on the complete database is to grant db_datareader access.

on the DB in a query

ALTER ROLE [db_datareader] ADD MEMBER [User]

If per table access is needed, you need to play with the security within the database itself. for example Connect to the database you want to do security

DENY DELETE ON [schema].[tablename] TO [user]

  • Currently i am encountering an error saying that : The server principal "Test" is not able to access the database "master" under the current security context. Cannot open user default database. Login failed. Login failed for user 'Test'. (Microsoft SQL Server, Error: 916) – Nathan Jun 19 at 13:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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