August 6, 2010 at 9:25 am
I'm moving from a W2k3/SQL2k5 environment to W2k8/SQL2k8. I've already configured (transaction) replication, which is working well. This managed to move the tables, views and stored procedures, but not the SQL users & not the permissions on the stored procedures.
Does anyone know how to generate scripts with the permissions for the stored procedures?
Thanks,
BigSam
August 6, 2010 at 9:38 am
I use a nasty cursor...
Run on the old server and copy the contents of the results panel to a query window on your new server. The script assumes the users already exist on the destination server/db
Also, this applies the EXECUTE permission, change to suit your needs.
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
-- 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
-- 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT ROUTINE_SCHEMA, ROUTINE_NAME , ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES ---
WHERE (ROUTINE_NAME NOT LIKE 'dt_%' AND ROUTINE_NAME NOT LIKE '%sp_MS%' ) AND
ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME NOT LIKE '%SqlQuery%'
-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN
-- 6 - Initialize the variables
SELECT @OwnerName = StoredProcOwner,
@ObjectName = StoredProcName
FROM #StoredProcedures
WHERE OID = @MAXOID
-- 7 - Build the string
SELECT @CMD1 = 'GRANT EXECUTE ON ' + '[dbo]' + '.' + '[' + @ObjectName + ']' + ' TO ' + '[USERNAME]'
-- 8 - Execute the string
--select * from #StoredProcedures
print @CMD1
--EXEC(@CMD1)
-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END
-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO
N.B. No warranty for this script is offered...
August 8, 2010 at 7:57 am
I found the solution in the publication. When adding or modifying the properties of the publication, in the Articles section, select Article Properties for each of tables, Stored Procedures, Views and User Defined Functions. Inside each of these is the option to copy or not numerous options, including permissions.
You can do this at the individual table, stored procedure, etc. level or for all entries by its category. Some settings are enabled by default, but many are not. My recommendation is to review all of these when creating the publication - it can save you a lot of hassle down the road.
If you modify the publication settings after having initialized the subscription databases, then you will need to re-initialize them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply