Sql-server – the permission needed to add permissions

permissionssql server

I'm creating an automated backup, upload, restore and user creation utility for a process here at my company. I've got everything up until the user creation all set. I've got a freshly restored database that I need to create a user in so that the application can connect to it.

So my problem is two-fold – I've got a user with sufficient permissions over master to restore the database, but I can't seem to figure out what permissions that user needs to be able to USE the newly restored database.

Once I've got that, I think I can use sp_change_users_login to reconcile the user with the database, but I can't seem to crack that.

BTW, if it wasn't clear, this needs to be done through T-SQL, unless it's a one-time permission that can be added through SSMS.

Best Answer

To change permissions within the restored database the account which the automation is using will need to be a member of the securityadmin fixed server role. That'll give you rights to create a user within the database and give that user permissions within the database.

Using sp_change_users_login requires that the login which is running it be a member of the db_owners fixed database role within the database you are running it.

Related Solutions

Sql-server – Receiving “The SELECT permission was denied on the object” even though it’s been granted

I'm not sure here, but I'm going to go out on a limb. I think your issue might be with your DENY CONTROL record. See here about half way down the page:

Denying CONTROL permission on a database implicitly denies CONNECT permission on the database. A principal that is denied CONTROL permission on a database will not be able to connect to that database.

I realize that example is for a database, but take it one more granual level. A DENY CONTROL on a table will deny all privileges on it, I'm guessing. Do a REVOKE CONTROL to get rid of that and see if that fixes your issue.

If so, you'll have to place the user in a database role or deny them the explicit privileges against the table.

SQL Server Job Permissions – How to Grant Permissions to Run a Job

You could create a stored procedure that runs the job. You can use with execute as owner to run the stored procedure as the database owner. That way, the users themselves don't need permissons on sp_start_job.

create procedure dbo.DoYourJob
with execute as owner
as
exec sp_start_job @job_name = 'YourJob'

Grant execute rights on DoYourJob to allow people to start the job.

Of course, you can also put the contents of the job in a procedure, and grant rights to run that. That would allow more interaction, like displaying a result text.

Best Answer

Related Solutions

Sql-server – Receiving “The SELECT permission was denied on the object” even though it’s been granted

SQL Server Job Permissions – How to Grant Permissions to Run a Job

Related Question