April 3, 2009 at 5:13 am
Hi All,
Can any one provide a script for extracting all users permissions in a database (SQL2005). Actually we need to refresh several databases from Prod to Dev environments frequently and the users on Prod will be granted readonly access and hence when the database is restored on Dev it will overright the current one and the users will have the read permissions. So, before refreshing the database, we need to generate the script of user permissions.
FYI: I tried with generate script from all tasks by selecting the object level permissions and it is of no use.
Thanks in advance,
Kiran
April 3, 2009 at 5:23 am
kiran.kamuganti (4/3/2009)
FYI: I tried with generate script from all tasks by selecting the object level permissions and it is of no use.
you havent tried very hard then have you!
either the user mapped to the database is part of a database role and has no object permissions defined or there are no users currently mapped to the database!
i used the script wizard for a test database that had a user pez mapped as dbowner and produced the following
USE [test]
GO
/****** Object: Table [dbo].[diskmon] Script Date: 04/03/2009 12:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[diskmon](
[recdate] [datetime] NULL,
[drive] [varchar](5) NULL,
[freespace] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 04/03/2009 12:17:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from sys.databases
END
GO
I then used the script wizard against the same database this time with no db role but securables defined against the user pez and received the following script
USE [test]
GO
/****** Object: Table [dbo].[diskmon] Script Date: 04/03/2009 12:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[diskmon](
[recdate] [datetime] NULL,
[drive] [varchar](5) NULL,
[freespace] [varchar](5) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[test] Script Date: 04/03/2009 12:20:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[test]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT * from sys.databases
END
GO
GRANT EXECUTE ON [dbo].[test] TO [pez]
GO
GRANT DELETE ON [dbo].[diskmon] TO [pez]
GO
GRANT INSERT ON [dbo].[diskmon] TO [pez]
GO
GRANT SELECT ON [dbo].[diskmon] TO [pez]
GO
so, the question is, what is the status of your user(s) mapped to the database you are scripting?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply