User permissions

Asked

Viewed 398 times

3

I’m having a hard time configuring a user’s permissions in sql server:

Today I have a user who is currently the db_owner, I want to take this access from him, but I need this user to have full access to procedures and functions, and at the same time that it only has query access in normal tables, how can I make this configuration?

sql-server

1 answer

2

  • Remove the user from the group db_owner;
  • Add the group db_datareader so that you have permission to SELECT;
  • Add specific permission from EXECUTE in the procedures you want it to perform.

Example: 

GRANT EXECUTE ON nome_storedprocedure TO login_usuario

  • and can you select all proceures? Pq are more than 2,000 per Databases

  • If it’s one-on-one it’s gonna take too long

  • you can give permission to run for the entire bank, which will allow you to perform all procedures: GRANT EXECUTE TO login_usuario or in the squema, for example in the dbo that way: GRANT EXECUTE ON SCHEMA ::dbo TO login_usuario

  • there it will be able to give update, alter and etc in all procedures?

  • the procedures continue with a lock lock running the script , only unlock if I go in the role of the database and release, but to do this a ha one is unfeasible, because there are many

  • if you give permission the schema works, but you can do something more elegant: creates a new role: CREATE ROLE db_execproc, add this permission to the user, and finally give permission to run in the database for the new role: GRANT EXECUTE ON DATABASE::nome_database TO db_execproc

  • But the procedures are still padlocked next door

  • I did a test with the local DB and ran the run, I did not notice if the procs appear with the lock I will check

  • I tried so many ways, and I couldn’t

Show 4 more comments

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.