While web hosting I use Sql Server 2000 as the database. Imagine I have hosted 3 Web Sites. All these 3 users want to modify/update their designs. What I did is I created respective 3 users having access to the respective databases only. So that they can registerd the ip and add to the Sql Server 2000 Enterprise Manager. These users are able to access their own databases only. But all these 3 users are able to access the default databases like master,pubs etc. How to restrict this.
Also suggest which is the optimal way to give control to the respective users while using Sql Server 2000.
I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example - Properties - New Folder - New Data Source - My Subscriptions - Site Settings - Search etc.
How can I disbale or hide all these options so that all the user sees is the list of reports?
I am developing a report using SQL Server Reporting Services 2005. Now I need to restrict the Export to Excel functionality for some users, but allow it to others, on a given report (Not all reports). Please let me know if it can be done, and if Yes, then How?
in cube we have one role defined. users in that cube should be allowed to see only last 2 days of data. I mean they should be able to browse only last 2 days of data based on current date. How to implement?
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20)) FROM sys.dm_exec_sessions s INNER JOIN sys.dm_resource_governor_workload_groups g ON g.group_id = s.group_id ORDER BY g.name GO
Following is the code to create pool, group,classifier function:
USE master GO; -- Create a resource pool pool_login. CREATE RESOURCE POOL pool_login WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin USING pool_login; GO CREATE WORKLOAD GROUP GroupDBNAME USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1); GO
In sql2k you used to be able to have a same user in 2 databases under the same login. Just moved to 2005, using sql authentication, and have some users who need to access 2 databases using the same login credentials. But sql2005 will not let me create 2 users under the same login across databases. Any ideas?
I am going to give user rights for an external user to connect to my SQL Server via Client Network Utility.
Atlough I have given user permissions to only access one database and not the whole list, how do I make sure that they cannot see all the other databases on my SQL Server?
I have 20 instances of databases on my SQL Server and ideally I would like to give 20 different people access - but each of them when they enter my SQL Server, should not even know that the other databases exist.
I have a third party application that creates several databases on my sql server. This party uses the same user for accessing all this databases. However after moving to a new server this databases the user is no longer able to query them whith the exception of the "sa" user. Could anyone explain me how to add permissions to this databases manually so the user can query them again.
Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.
I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.
I have a client who would like to access his database via SQL Server Management Studio. I created a login for him. This login has access only to his database on the server (I set this up using the user mapping), the Securables has Connect to SQL checked (otherwise he won't see his database). When I test this login, he is able to see all the other databases on the server, though when he clicks on the ones he doesn't have access to, it will tell him that he can't access that database. He can access his database alright. My question is, can I hide all the other databases from him, and only display that only database he has access to? I have tried many different ways, I can't seem to be able to do so.
Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases. We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
I have a requirement to delete all the orphans users for the databases. The issue I am having is with when database principal owns a schema in the DB, User cannt be dropped.
How do I transfer it to DBO in case I am looping multiple databases. This is what I got so far .
declare @is_read_only nvarchar (200) Select @is_read_only = is_read_only from master.sys.databases where name='test' /* This should be a parameter value */ IF @IS_READ_ONLY= 0 BEGIN Declare @SQL as varchar (200)
We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.
I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.
Is there any additional step I should/could take to get this active?
I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name) at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries". now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country} who has an idea how to solve this problem?
What would be the best practice to prevent users who didn't create a record in sql from deleting? When a record is created I have the username who created the record in one of the fields. I was thinking maybe a query?
I have an ETL product, every 30 minutes it loads maybe a coupla 100K records of various construction into an SQL Server Db (2000 SP3, on Win 2000 Std Server also Win 2003 Ent Server). It really hammers the Db with a combination of batch insert update and insert fallback to update etc. It does not use 100% CPU actually and is IO bound. Works a treat.
Now the problem, someone has built a .Net ADO based front end, and for the 5 minutes my product runs every 30 minutes they have a hope in hell of getting a reasonable response for their select queries. Now my take is that maybe I can delay for 5ms after each transaction I do to allow the other user processes a chance. But better maybe would be for my ETL product Db user to have a lower priority than other Db users.
What is the best way to achieve this end? If I understood more about how SQL Server prioritizes users and their requests it might help. (round robin?)
I have an ETL product, every 30 minutes it loads maybe a coupla 100K records of various construction into an SQL Server Db (2000 SP3, on Win 2000 Std Server also Win 2003 Ent Server). It really hammers the Db with a combination of batch insert update and insert fallback to update etc. It does not use 100% CPU actually and is IO bound. Works a treat.
Now the problem, someone has built a .Net ADO based front end, and for the 5 minutes my product runs every 30 minutes they have a hope in hell of getting a reasonable response for their select queries. Now my take is that maybe I can delay for 5ms after each transaction I do to allow the other user processes a chance. But better maybe would be for my ETL products user to have a lower priority than other users.
What is the best way to achieve this end? If I understood more about how SQL Server prioritizes users and their requests it might help. (round robin?)
Hi guys, say i wrote a query that returns 1,000 records.. what kinda query could i write that only returns say the first 50 records of the 1,000 recs..
I need to restrict delete from one table by any user of SQL. How can we do this? This is our master table and we dont want any one to delete data from this table.
Is there a switch in 7.0 that I can set if I don't want users to see a certain column in a table.
Ex: TableA(column1, column2, column3) If they do a select * from tableA they will receive information on all three columns, I don't want them to see the last column3.
Is there any way of not allowing users with MS Access from using it to connect to a SQL 7 server? Basically, no connection to SQL through MS Access should be allowed. Any ideas?
