Sql-server – How to determine which permissions needs to be assigned to the newly created login

functionspermissionsrolesql server 2014stored-procedures

We have 4 databases which contain different procedures and functions. Till now the actual application can access the database using only the sa account, now I would like to change the app to use something other than the sa account. For that I need to create a new SQL Login and assign to that login certain permissions. Is there any way like script to determine what permissions I need to assign to the newly created user?

Best Answer

The link by @Ross will definitely help you determine syntax and what all of your options are, but for a little more guidance:

There are two database roles that will probably be useful to you, they are db_datawriter and db_datareader. The two of them will probably handle the majority of your CRUD (Create, Read, Update, Delete) operations.

ALTER ROLE db_datareader ADD MEMBER <user>
ALTER ROLE db_datawriter ADD MEMBER <user>

You can get more granular than this if you want/need. For example, allowing inserts, but not updates or deletes to tables.

You mentioned stored procedures in the tags, so those can be a bit trickier. One of the easier methods is to grant execute on the schema. But you can also grant them one at a time.

--all stored procedures in schema dbo

--specific stored procedure
GRANT EXECUTE ON dbo.uspDoMyThing TO <user>

Finally, if you are allowing the application to do TRUNCATE then they will actually need the ALTER TABLE permission in addition to db_datawriter.

GRANT ALTER TABLE ON dbo.MyTable TO <user>

Congratulations on taking control of the security of your server properly. Even if you have to end up granting db_owner in the database, that's far better than sysadmin.

Some final notes:

  • Explicit deny permissions take precedence over grants.
  • Assigning permissions to schemas will grant permissions to all objects in that schema (even if they get created later).
  • I would start with db_datareader, db_datawriter and EXECUTE on all stored procedures and that will get you going the fastest with the least maintenance required later. It may be broader than necessary but the damage that can be done is minimized.