SQL Server – Using sp_addrolemember in Stored Procedure for Different Database

sql serversql-server-2012t-sql

I have a stored procedure to restore the latest backup of a prod database to an acceptance database. Once it's restored I need to grant a user read access to the database.

Use statements are not allowed in stored procedures, and both ALTER ROLE (it's a SQL Server 2012 box) and sp_addrolemember don't take a target database parameter.

Ideas?

Best Answer

EXEC('USE mydb;ALTER ROLE myRole ADD MEMBER myMember;');

But I am not quite sure what you are trying to achieve. After restoring a database to a different server, you need to fix the link between users and logins. For that you can execute this in your database:

ALTER USER myUser WITH LOGIN myLogin;

Or use the same EXEC('USE...') method shown above with that ALTER USER statement. That should get you where you need to be. All the database related permissions are stored in the database itself, so they get restored with the database.

Related Solutions

Sql-server – SQL Server – DBO without Backup

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema). This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.

Sql-server – Using the correct database when calling a system stored procedure in SQL Server 2008

You need to mark your stored procedure as a system object to get the behaviour that you want (full example below tested as working on 2008 SP3)

USE master;
GO

CREATE PROCEDURE dbo.sp_mysp
AS
SELECT *
FROM INFORMATION_SCHEMA.TABLES

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from master*/

GO

USE master;
EXEC sys.sp_MS_marksystemobject sp_mysp

GO

USE tempdb;
EXEC dbo.sp_mysp /*Returns tables from tempdb*/

This is an undocumented approach and may not work in future versions.

Best Answer

Related Solutions

Sql-server – SQL Server – DBO without Backup

Sql-server – Using the correct database when calling a system stored procedure in SQL Server 2008

Related Question