SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Can you script role permissions


Can you script role permissions

Author
Message
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
Can you create a SQL statement (that will end up in a stored Proc) that sets all of our custom views ('zvw%') with Insert, Select, Update access.. for a given role?

Trying not to have to go in and add new views and tables.. everytime a someone forgets to add their new view/table to the correct roles.
sturner
sturner
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2218 Visits: 3259
Here is a select snippet that will return user and role permissions on objects in a database. I have a proc that I run every day that gathers logins, users, roles and their permissions and dumps it to a file for auditing purposes.

select protecttype, action, so.name, s.name from sysprotects sp inner join sysobjects so on so.id=sp.id inner join sysusers s on sp.uid=s.uid and s.name <> 'public'

The probability of survival is inversely proportional to the angle of arrival.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
[code="sql"]DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + 'GRANT INSERT, SELECT, UPDATE ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name) + ' TO [YourRoleName];
'
FROM sys.views
WHERE name LIKE 'zvw%';

PRINT @sql;
--EXEC(@sql);
code]

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
I decided to break this into two roles, working on the basic access first and have the following issue, but not sure why the code is creating this:

The last object is not getting its full name, is there a limit on what gets printed? There are 284 views, and only 47 are getting returned.. I am running in ssms window, testing before I put into a stored proc.
-----
DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + 'GRANT SELECT ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name) + ' TO [ro_ZemeterNet_Access];
'

FROM sys.views
WHERE name LIKE 'zvw%';

PRINT @sql;

------
GRANT SELECT ON [dbo].[zvw_QTZ_dummy_customers] TO [ro_ZemeterNet_Access];
GRANT SELECT ON [dbo].[zvw_Daily_Fcst_Curre
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
dwilliscp (6/24/2013)
is there a limit on what gets printed?

Yes. In SSMS 2012 go to Tools > Options > Query Results > SQL Server > Results to ... and increase the number.

Instead of PRINT though, put this at the end of your batch and you'll never have to worry about that truncation problem again:

SELECT  @sql AS [processing-instruction(query)]
FOR XML PATH(''),
TYPE;



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
opc.three (6/24/2013)
dwilliscp (6/24/2013)
is there a limit on what gets printed?

Yes. In SSMS 2012 go to Tools > Options > Query Results > SQL Server > Results to ... and increase the number.

Instead of PRINT though, put this at the end of your batch and you'll never have to worry about that truncation problem again:

SELECT  @sql AS [processing-instruction(query)]
FOR XML PATH(''),
TYPE;



I am using SQL 2008R2... Not sure writing it to a xml file is the better option.. how about a cursor? I made some sort of mistake though ...

DECLARE @sql NVARCHAR(MAX) = N'';
DECLARE @l_name varchar(MAX);

SET nocount on;
DECLARE i_Cursor insensitive cursor

FOR
SELECT [name]
FROM sys.views
where [name] like 'zvw%';

open i_Cusor
FETCH NEXT FROM i_Cusor into @l_name

while @@FETCH_STATUS = 0
BEGIN

SELECT @sql = @sql + 'GRANT SELECT ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name) + ' TO [ro_ZemeterNet_Access];
'
FROM sys.views
WHERE name LIKE @l_name;

PRINT @sql;
--EXEC(@sql);
FETCH NEXT FROM i_Cursor INTO @l_name
END
GO
CLOSE i_Cursor
deallocate i_Cursor
set NOCOUNT off;

error returned
-------------

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'i_Cusor' does not exist.
Msg 16916, Level 16, State 1, Line 14
A cursor with the name 'i_Cusor' does not exist.
Msg 16917, Level 16, State 1, Line 1
Cursor is not open.
Orlando Colamatteo
Orlando Colamatteo
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14875 Visits: 14396
dwilliscp (6/24/2013)
I am using SQL 2008R2...

SSMS 2012 can go against SQL 2008 R@. I think the setting is in the same place in SSMS 2008 R2. I was just pointing out the version I am using in case they moved it.

Not sure writing it to a xml file is the better option..

It's not writing to an XML file, it's outputting an un-truncated XML document. Then click on the Grid field to open the document where you can review the entire text. This is just for debug purposes, so you can see the un-truncated output.

how about a cursor?

no need to use a cursor...

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
dwilliscp
dwilliscp
SSC Eights!
SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)SSC Eights! (812 reputation)

Group: General Forum Members
Points: 812 Visits: 775
Oh... ok thanks.. will put you code back in and try it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search