May 22, 2020 at 8:15 pm
Hi,
I have scenario that i need to give access (select permission) to all views in a DB. I have given read permission to all tables, for windows AD account by which he is able to do select on all the view. Same user I have created SQL login and given read permission on the DB however he is good with tables to select but not view to select.
Version of 13.0
Question: is there any script which allows us to give select permissions to all views in a DB for both Windows ID (EX:- Company Domain\user name ) and SQL login (EX:- ABC). also you can reply to sns_subash2002@yahoo.com
As it is production hope to see a response from you all>
Thanks,
Srinivas
May 22, 2020 at 9:40 pm
Pretty sure putting the logins in the datareader role should handle this.
alternately, you could generate the script it with something like:
SELECT N'GRANT SELECT ON ' + name + ' TO USER' AS ToRun FROM [sys].[views]
Replacing "user" with the user you want to grant that on. Toss that into a cursor, then have a loop through the cursor executing the script. Something like:
DECLARE @user NVARCHAR(255) = N'USER';
DECLARE @SQL NVARCHAR(MAX);
DECLARE [curse] CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT SELECT ON ' + [name] + ' TO ' + @user + ';' AS [ToRun]
FROM[sys].[views];
OPEN [curse];
FETCH NEXT FROM [curse]
INTO
@SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
--EXEC [sys].[sp_executesql]
--@SQL;
PRINT @SQL;
FETCH NEXT FROM [curse]
INTO
@SQL;
END;
replace"USER" with the name of the user you want to grant it to and uncomment the EXEC command if you want it to actually run. Otherwise it just prints the commands to run to the messages tab.
NOTE - the above assumes all of the views are in the default schema. If not, you'd need to look up the schema for those as well.
This will work for all views in that database.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 26, 2020 at 9:35 pm
Hi,
Thanks for the help. this was useful.
Thanks,
Srinivas
August 9, 2020 at 3:49 pm
Hi All,
Sorry for posting it to wrong place. I need some urgent help that is thee reason I'm posting here.
I'm using SQL Server 2017, I need to give developers only to view the jobs (either it is SSIS packages which is scheduled as a job or SP scheduled as a job).
What does edit means:- Double click the job, go to each step and see which package it is referring to and see the configuration/data sources connections for each step. I'm happy if i don't give me rights to run the job/delete the job/ and modify the job.
one more thing. I know there is a role by name "SQLAgentReaderRole", i can assign but it is a combination of (All of the SQLAgentUserRole rights) + The ability to review multiserver jobs, their configurations and history. So now if i give rights to this role "SQLAgentReaderRole", and if he doesn't own this jobs still can he view the jobs and see the parameters and edit the configuration of the data source, if ssis package is scheduled as a job
Any help is really useful, as I need to do this change on production for the developer on Monday.
You may send mail to me at sns_Subash2002@yahoo.com
August 9, 2020 at 3:55 pm
Hi All,
Sorry for posting it to wrong place. I need some urgent help that is thee reason I'm posting here and i didn't see where to post the query as well.
Summary:
We had migrated SSISDB from SQL Server version 11.0.6260 to SQL Server version 13.0.5830.85. we noticed that SSISDB on SQL Server 2016 is at healthy state later after we restore and had converted the compatibility to new version. We have all the projects/packages/environment variables came as ese. Even in SQL agent job, when we open the steps and see the package configuration/and datadource they also turned up.
How did we get the backup from the old server SQL Server version 11.0.6260 For SSISDB: We had taken native backup and have backed up master key with encrypted password.
How did we restore on new server SQL Server version 13.0.5830.85 on SSISDB: we restored using move with, decrypted the password (for the above master key) and encrypted with new password.
How did we create SSISDB catalog: We created ssisdb integration catalog with normal standard have a password to it.
Note:- We are also trying to set up AG for SSISDB.
Issue: We are having 2 issues.
Error 1:
While we setting up for AG for SSISDB, as always we need to give the password of SSISDB catalog at the time to creation, while adding to AG. Now when we give the password, it is complaining as incorrect password (this is the same password which i used at the time of creating the SSISDB catalog).
Error for 2:-When we run the schedule SSISDB jobs it is toughing below error.
Please create a master key in the database or open the master key in the session before performing this operation. Source: .Net SqlClient Data Provider Started: 10:38:07 AM Finished: 10:38:07 AM Elapsed: 0.187 seconds. The package execution failed.
I need step by step resolution for both above issues, as i have tried multiple methods to get rid off error2. Working on masterkey and all..
We really appricate if you turnup quickly, so that users/down stream system are not getting effected.
August 10, 2020 at 2:35 pm
Both of those questions should be new posts as they are unrelated to the original post. Replying to an old post with an unrelated question is not likely to get you any replies.
As for the first question, I generally don't let developers look at the jobs. Where I work, the DBA owns the jobs and the job steps, so I've never looked into those permissions. That being said, a quick google brought me here:
First answer -
Did you try the TargetServersRole in msbd?
By default, this role deny the create/start/stop jobs and allow the user view all the jobs in the instance.
As for the SSIS one - error 1, are you using the original password OR the new password? I would try both and see which one it likes and then change it to use the appropriate one. Error 2 - you probably need to modify the master key to allow encryption by the service master key.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 16, 2020 at 5:36 pm
thnaks for the reply. I used MSDB role SQLagentread role
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply