On our report server I have several folders. I would like to set up security such that a user doesn't see any folders except the "My Reports" folder (I enabled the My Reports site option) and a few other folders I specify. These are a shared folder and the models folder.
Is there a way to create a system level role that by default can't browse any folders except My Reports and ones I specifically grant? I would also like this person to default to having the item level security role of report builder to create reports off of report models.
The only way I have been able to accomplish this so far was to add the user to the the system users site level role and then go to every undesired folder and delete the item level role assignments for the user. It seems odd for the user to have access to every folder then to remove permissions from each folder. Should I create a role with no tasks and then selectively add in the permissions to the desired folders?
I did try creating a new system level role that only had the Execute Report Definitions task assigned and assigned the user exclusively to this role but they could still see all of the folders and their My Reports folder. When I created a new folder the user had been defaulted to item level roles of Browser and Report Builder. I suspect these defaults are what caused the new folder to be visible as soon as I created the folder.
I searched the forum for my question so hopefully I am not causing a re-answer of this question.
Hi, I have a folder structure of reports like this Home | / A B
Now, suppose I have created this folder, A first and user, say A_User is given permission to view this folder. I am expecting this user to browse from 'Home' to A folder but as you know to browse folder 'Home' he must also be added to 'Home' folder . So I add him to 'Home' now.
The problem comes when I add new folder B, for user B_User. The moment I add this folder A_User is also added to folder B because of the inheritance of users to child folders. So user A is able to see both A and B folder....and thats my problem, he is supposed to only A folder and not B
Is there any way to disable this inheritance feature ?? I tried deleting A_User from B with a script using "InheritParentSecurity" method of ReportingService but it says that it can not delete inherited users.
How can I fix the problem ? without need to manually delete users from folders ??/
I want to use an Active Directory security group that is a Distribution List for a new role assignment for an existing report. Can someone tell me if this is possible? I get an error each time I try:
The user or group name <DLName> is not recognized. (rsUnknownUserName)"
Our SQL2000 server now allows all member of the Windows 2000 local administrators group log in with SysAdmin role.
I only want couple of people with sysadmin role. What should I do to prevent that. I was told once that I should delete the BUILTINAdministrator ID and manually add each window login ID to SQL server. Am I on the right track?
We have an application use Approle to read from database. If the client login to windows as administrator or a name that has the administrator rights, the application can get all data. If the client login to windows as a domain user that has limited rights, the application can't get all data. I run profiler and found that it seems, when application use approle to access a database, the login name is the domain user that log into windows. Is there anybody know what type of right the window login name should have in order to get all data from a database?
Second question, when I log in to window as domainusername( username is not administrator, but has administrator rights). In the profiler, I can see the application use this domainusername access database. However, under sql server login node, I didn't find domainusername. Is this because, the domainusername belongs to buildinadministrator?
I am attempting to set up a new user that has only the ability to run reports in the report manager.
I have created a new ActiveDirectory entry for DOMAINReportUser. I have a created a new folder with the reports, and have set this user as a Browser role on this directory, and all reports in the directory.
I have made it throught the security maze to gain access to these reports as this user, but I cannot dynamically run the reports. As the BUILTINAdministrator (content manager), I get a grayed background on the parameters and can run the reports. As this DOMAINReportUser, I get what looks like HTML parameter items, and cannot run a dynamic report. Even if I change the role of this user to Content Manager, I still cannot run a dynamic report.
How do you properly set up a user to be able to dynamically run, and only run, a report in Report Manager, and have this user only see reports in a single folder?
I have been fighting the security issues of creating a RS site and properly setting up access, and have yet to find a single site or person explaining the entire process in any coherant method.
Hiiiiiiiiiiii, Why my program can't access data base, if i'm not set permission & security to All User (Everyone)..?? FYI, my IIS setting for directory security is Basic Authentication. Pls advise how my program can access the program, if permission & security are not set to Everyone ?? Thanks
There is a STIG Check that does not allow grant "Connect SQL" directly to any logins except SQL System and the SA account. My way of resolving this is to do the following:
Step One: We create a Server Role called SQL_APPLICATIONS – for the application accounts We create a Server Role called SQL_DBA – for the DBA accounts and give them direct “Connect SQL’ server permissions. MAKE THE ROLE OWNER = sysadmin (group)
Note: I think that creating a Server Role is only available starting with SQL Server 2012, but not sure. I am using SQL 2012
Step two: I add the members (Logins - SQL & Windows) – in this case any application accounts and DBA accounts to the new roles respectively
Step three: I remove the “Connect SQL” Permission from each Login
The first problem i noticed is that the maintenance plans failed with "The owner domainusername of job db_backup does not have server access.I am currently using a test system and wondered If you think I will have trouble with the application connecting when I try and implement on the production systems.
I have a report running and I am attempting to assign role based security. I added a group to the site level security. The group I added contains child groups. It doesn't seem that report server is looking into the child groups to see if the logged in user is a member of the child group. Is there anyway to get this to work instead of adding all the groups directly? I suspect that report server is using cominterop and cominterop is not traversing the directory tree?
In the role-based security model that is implemented for Reporting Services, users who are assigned to the Content Manager role can create and edit reports in Report Builder. Local administrators are automatically assigned to this role.
If you want other users to be able to work with a Report Builder report, you must create a role assignment for them that includes the default role "Report Consumer." Alternatively, you can create a custom role definition. As long as the customized role includes the "Consume reports" task, users who are assigned to that role will have sufficient permission to create and modify reports using Report Builder
---------
However, when I try to give a contact those rights, the "Report Builder" link still does not show up for my users. Is there any other place where I can examine the security rights to see why the Report Builder permission is not granted to my users?
I am setting up some security requirements - the requirement is that team leaders get to view all teams within their division. What I would like it to do when they log onto their dashboard/ssrs report is that it defaults to the team leaders default team (they can still select from other teams if they require).If I can avoid I don't really want another parameter in the report for this.
In my cube I have an attribute under the people dimension (where teams and division also reside) called Fee_Earner_Effective_Flag and where this is set to Y this is their current team.
Under the Role properties security under the Fee_Earner_Effective_Flag attribute I have set the Default member to
When I then logon as the user and try to access the cube I get the following
Errors in the metadata manager. The '[People Primary Feeearner].[Fee_Earner_Effective_Flag].[Y]' security default member from the '{' attribute returns a result from a different hierarchy.
I have also tried setting the default member in the cube solution and then processing but then the cube failed.
I have setup a new SQL 2000 SP4 and internal auditor query about revoke permission from Public role and remove guest from all databases.
1. Can I revoke all default permissions (select on system tables in all DBs) from "Public" role? I am concern any error after such action.
2. I found that guest account in DB -- master, tempdb and msdb. According to Microsoft documents. The account should not remove and can't from master and tempdb. How about msdb?
Could someone please advise if the report folder structure I am proposing will be secure enough so unauthorised users can't access the reports?
I have created three folders; Departments (that contains subfolders being one for each department), Sections (that contains subfolders being one for each section), and Open Access. A manager will either see the Department or Section folder and then the relevant subfolder for the area he/she manages.
I have deployed the reports from the Development Studio to the Home page, made them invisible there, but created linked reports that then show in the subfolders. Each subfolder contains a separate linked report and each has parameters set within the Properties tab of SSRS that only enables the manager who will access that subfolder to see the report details specific to their area.
A manager will then be given the browser role for only the subfolder he/she has access to.
I would like to be sure that a manager will not be able to see information in other subfolders. Will my proposed structure be secure enough?
Hi, The Report Manager portal has many folders. For each folder there are specific users with different roles. I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.
Use ReportServer
SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r
WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID The above query fetches all the users and their roles. The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query. TIA
I have a business requirement to build a tabular data model, where I need to mask information of other Agents from a given Agent but I still need to show the overall sales of the given product.
For eg: IF an Agent is in APAC region he should see APAC region sales and also should be able see the sales of the same product in other region without knowing region specific break down.
For Agent "Tom" in APAC region, the numbers will look like this APAC_Sales = 100,000 Other_Sales = 500,000
And if "John" is in NA region, then the number will look like this for him
NA_Sales = 200,000 Other_Sales = 400,000
I wanted to create "Roles" based on the Region, so all the agents belong to "APAC" region will have same view as Tom and "NA" region agents will have John's view.
My company has a Windows 2008 R2 server which is running SQL Server v11.0.5058. This server was previously running SQL Server 2008 and was recently upgraded. Since the upgrade I have noticed that when I connect to this server using SSMS and Windows authentication it seems as though I have a limited user context as I cannot see SQL Agent in the server tree at all and underneath the server security > logins folder I can only see the sa and SQL Server Windows service accounts (there are many more).
If I connect to the server using SSMS and the sa credential then I can see everything I expect to be able to see as a sysadmin.
I tried connecting as sa, then deleting my Windows AD account from the security > logins folder and reading my Windows AD account with the sysadmin role however this yielded the same result, when I connect using Windows authentication I still appear to be in a limited user context.
We have several other SQL 2008 / 2012 servers within our organization and all of them appear to be working fine / none of them exhibit this problem.
I have windows 2003 reporting services 2005 that has been working fine. Now all of a sudden when I go to http://localhost/reports I am unable to assign security to folders(the link is hidden) the New data source button is hidden, the new folder button is hidden, upload folder and report builder are also hidden. I am logged in as aministrator.
Also when I connect to reporting services through SQL Mgmt Studio I have the same issue, there are no options for permissions only a general option.
I'm sure it's a permissions issue but what do i do to resolve?
An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
I am working on the security model for an application that will be used by 100s of users with a dedicated SQL 2005 database for this application and access via SQL XML Web Services.
The client has asked to make it "open" during alpha testing such that anyone can access the web services without having to set them up first. Is there a way to do this? The best I can figure is to use mixed mode security and hard code a login and password. Any method using Windows authentication would require that I add every user at a minimum to the database.
In production, all users will have an active directory role specified that determines if they should have access to the web services or not. However, it is my understanding that to use Windows authentication, I would still need to add each individual user at a minimum as a Login to the SQL Server, and under best practices also as database users with permissions granted to the endpoint.
Am I correct in the above, or is there a more efficient way to achieve these results?
I am setting up a database for one contractor who needs to access one of our databases but i need to remove the permission for them to view the security folder so he cant access the SA account and enable it. I was able to deny them the view permission to see all the other databases and hide the system objects but i can still see the security folder and the sa account there where i can disable and enable it.
If user want to see the grand total for a measure with include all members, even though the user has limited access for that member, so how we can do using DAX?For example, let’s say the total revenue for all the divisions in a cube is $15,000. You create a role called “Division A”, and set it up so members of that role can only see the revenue for Division A, which totals $3,000. If you use a front-end tool like Excel to access the cube and use the division hierarchy to see the total revenue, you will see the revenue of $3000 for Division A, but also want to see the Grand Total for the revenue as $15,000How we can achieve above scenerio in tabular model (DAX).
I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
1. I have sql 2008 R2 running on my LocalHost. 2. Created Data Base [Customer]. 3. Created Linked Server [CUSTOMERLINK] USING Microsoft Jet 4.0 to link to Drive F:Data which has DBF files in it. 4. Create dbo.Customer_Upload Table. 5. INSERT INTO [Customer].[dbo].[Customer_UpLoad] ([Name],[Email]) SELECT NAME,EMAIL FROM [CUSTOMERLINK]...[CUS]
All this works fine. I can even put it in to an After Insert Trigger on another table and it works.
My problem is that I need this to work in a scheduled job.
F:Data is just a folder with files in it.
This info is from a Restaurant POS system and I need to update it every night.
I have tried every which way to to setup the security issue as there isn't any login security on the folder and SqlServerAgent wants security.
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message "cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again." After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message. So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
I have a set of folders with the following structure:
MainFolder
1999
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
2000
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
etc.
Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this. declare @inpath varchar(100) SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"' EXEC @filestatus = master..xp_cmdshell @inpath but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder. I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
