June 21, 2013 at 8:54 am
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.
June 21, 2013 at 9:08 am
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.
June 21, 2013 at 10:33 pm
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
June 24, 2013 at 1:12 pm
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
June 24, 2013 at 1:34 pm
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
June 24, 2013 at 1:54 pm
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.
June 24, 2013 at 1:57 pm
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
June 24, 2013 at 1:59 pm
Oh... ok thanks.. will put you code back in and try it.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply