Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Can you script role permissions Expand / Collapse
Author
Message
Posted Friday, June 21, 2013 8:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
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.

Post #1466228
Posted Friday, June 21, 2013 9:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 2:38 PM
Points: 1,426, Visits: 3,223
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.
Post #1466242
Posted Friday, June 21, 2013 10:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
[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
Post #1466435
Posted Monday, June 24, 2013 1:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
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
Post #1466872
Posted Monday, June 24, 2013 1:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
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
Post #1466883
Posted Monday, June 24, 2013 1:54 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
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.
Post #1466889
Posted Monday, June 24, 2013 1:57 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:10 AM
Points: 7,081, Visits: 12,573
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
Post #1466891
Posted Monday, June 24, 2013 1:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:33 AM
Points: 310, Visits: 537
Oh... ok thanks.. will put you code back in and try it.
Post #1466892
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse