I need a script to kill existing sessions on [db1] and [db2] except sessions from [user1]. I have a script like this now:
USE master
GO
DECLARE @kill varchar(8000) = '';
SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), spid) + ';'
FROM master..sysprocesses
WHERE dbid = db_id('db1)
EXEC(@kill);
I just need to extend to run on db2 and don't kill sessions from user1
I want to be able to list all users connected/logged in a specific database and disconnect them all or a certain user.
can this be done in SQL and if so how?
Does anybody have a job where it will disconnect certain users from the database
What I would like to do is schedule a job that will run at 11:00pm that will disconnect certain users (not all of them). This program should also kill any jobs that these users may be running.
I running into a problem occasionally, where a certain program hangs up and will not release its DB locks. This then keeps a couple of the nightly batch jobs from running.
(I run on SQL SERVER 7.0 on a NT server)
Matt Atkinson, mratkinson@www.com
Is there a way to forcibly disconnect users from a db with t-sql? I'm doing a nightly restore of a db and users who shouldnt remain connected after hours, are. So I cannot restore the db unless I disconnect them as the db cannot be obtained for exclusive access.
Any thoughts on this?
Edward Hunter, Data Application Engineer
comScore Networks
Dear All,
i've to drop one database....i'm sure....
but it is saying presently it is in use....
there no no replication....
some user is using....
that is a new database.....how can i throw all users from the database
Vinod
Even you learn 1%, Learn it with 100% confidence.
I'd like to find out who is accessing various databases and log the time they did so.
Is there a way to do this? I just need the account name and the time logged and then to write to a table so I can query it.
Hi All
IF some users are still connected to the database and I want to shutdown the server How can I disconnect them in order to shutdown.
Sincerely.
Hello,
I need to attach and detach 50 DB's, for which I have wrote a simple script. How can I make sure that there are no connections or users connected to the DB's, if there any users how can I forcefully disconnect them.
Thanks
Dakki
hi,
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?
Thanks,
Mat
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.
====Suresh, P.R, Postal Training Centre, Mysore.
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.
Thanks.
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.
Thanks in advance
brgds
Miguel
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.
thank you
Does securityadmin Server level role can add, alter logins and corresponding users on all databases ?Â
If not what is the best role other thn SA to have to manager logins and users.
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.
View 6 Replies View RelatedBased 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.
Thanks in advance.
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)
[Code] .....
I am using a web service to search for users by either their name or their nickname. Users can type the persons name into a dot net text box that has a Autocomplete extender - which means it returns a list of user with each keystroke.
This works fine if I just search for a name.
select r.fname +' ' + r.lname
from users u
join log l
on l.riderid = u.riderid
where fname +' '+ lname like @item +'%'
group by l.fname , r.lname, nicknamellame
So if I am searching for Dan Brown When I enter a D I get all users whose name starts with D, A reduces the list to those that start with Da until you get a small enough list to find Dan Brown. This works - what I would like to allow is if they entered the users nickname (another column in the users table) it would also include those options: So, if Dan's nickname is DannyBoy typing that in would return the nickname.
select r.fname +' ' + r.lname
, nickname
from users u
join log m
on l.riderid = u.riderid
where (fname +' '+ lname like @item +'%')
or (nickname like @item + '%')
group by r.fname , r.lname, r.nickname
having max(l.[date]) >= '2014-01-01'
This does not work
My insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. Â But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50)
CREATE TABLE #ActiveUsers
(
ID INT IDENTITY NOT NULL
,EmployeeID varchar(50)
,processed int
)
Create Table #Data
[Code] ....
I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month)Â in following format:
3 months   6 months   9 months   12  months
number of distinct users     x          y          z            w
I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."
But now we have changed the concept to get the ISID (name of the user) Â and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .
After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.
if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.
Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).
Here is my sample  code:
I need to sum it up to get the total user's from both the table but I should get 1 instead of 2Â
SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*) Â from dbo.test1
where invited_by ='sam'
Currently I am using SQL server 2012 and would like to implement database audit specification on specific users in my database. These are the users in my database name Payroll :-
PayrollAndy.Bred - db_owner
PayrollArpit.Shah - db_owner
Payrollwebapp - db_datareader, db_datawriter, EXECUTE
web_payroll - db_datareader, db_datawriter, EXECUTE
In my database audit specification settings, I would like to capture any SELECT,UPDATE,DELETE and EXECUTE command for users PayrollAndy.Bred & PayrollArpit.Shah only since they owned db_owner access. However, I am unable to capture any single command from both users. I do not want to put 'Principal' as public since I just want to capture both users activity.
Is it I miss out anything? Is it because of windows login account?
I have a query running and returning 3 columns, user name, e-mail and device name
SELECT DISTINCT v_R_User.Full_User_Name0 AS 'User full Name', v_R_User.Mail0 AS 'E-Mail', _RES_COLL_DEV00144.Name
FROM Â Â Â Â v_R_System INNER JOIN
           v_R_User ON v_R_System.User_Name0 = v_R_User.User_Name0 INNER JOIN
           _RES_COLL_DEV00144 ON v_R_User.User_Name0 = _RES_COLL_DEV00144.UserName INNER JOIN
           v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
 Where v_R_User.Mail0 <> ''
ORDER BY 'User Full Name'
From here I would like to generate an e-mail to each user (like mail merge) to each user in the table an include their machine name. I can do it with PS, but rather have it run directly from SQL. Is it possible?
i have a report that runs on a huge table rpt.AgentMeasures , it has 10 months worth of data (150 million records as of today and will keep increasing). i have pasted my proc below , the other tables that are joined to this huge table do not have more then 3k records.This report will be accessed by multiple users (expecting 20 ppl). as of now this reports runs for 5 mins if i pull for 1 month worth of data. if it is wise to use temp tables.
ALTER proc [rpt].[Get_Metrics]
@MinDate DATETIME,
@MaxDate DATETIME,
@Medium Varchar(max),
@footPrint varchar(max),
[code]...
After cloning our production SQL 2012 server to make DEV environment, there is a bunch of databases in "Suspect" mode. This is because the DB's and Logs have got out of sync.I can clean up each DB with this script:
ALTER DATABASE DB_NAME SET EMERGENCY
GO
DBCC CHECKDB (DB_NAME)
GO
ALTER DATABASE DB_NAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DBCC CHECKDB (DB_NAME, REPAIR_ALLOW_DATA_LOSS)
GO
ALTER DATABASE DB_NAME SET MULTI_USER
GO
but this takes a long time replacing DB_NAME and executing for each DB.I tried putting it all into a script with variables, but I have done something wrong and it does not work. Also is there a way to just apply it to the suspect DB's?
USE master
Go
DECLARE @dbname sysname, @cmd varchar(1000), @cmd1 varchar(1000), @cmd2 varchar(1000), @cmd3 varchar(1000), @cmd4 varchar(1000)
DECLARE db_recovery_cursor CURSOR FOR
SELECT name from sysdatabases
OPEN db_recovery_cursor
[code]....
I am having an issue when trying to union all between two databases.
This is the error:
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
I have updated the collation in both DB to Latin1_General_CI_AS and I am still getting the error when running the query.
The queries run separately ok. why I am still getting the collation error.
I'm trying to detach my database.But i couldn't do that coz there are some users connected to it. So how can i disconnect them?is there any way to disconnect them from the DB?
Thanks.
Running server 2003. I am running a program from home and am telneting in. When I leave for over 1/2 hour without inputting anything it disconnects me. How can I reset this setting to several hours befor it will disconnect me?
View 1 Replies View RelatedI would like to SELECT all filegroup on an SQL server instance, is that possible?Or only per database?
View 21 Replies View RelatedI have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT
idname, name, address, address2, awardedtoname, suppno
FROM contract INNER JOIN
house ON contract.idname = house.idname)
JOIN
(SELECT DISTINCT
tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
[Code] ....
How I could combine the results?
I need to compare columns in tables on 1 database on one server versus the same on a 2nd server.
I'm looking for added columns in dbase 1.
Is there a system T-SQL script that can be used for this?
The package has 15 DTS 2000 tasks that execute sequentially, depending on 15 successes. The 15th task then invokes 11 more DTS 2000 tasks simultaneously which each invoke a mail task on their success. The package is saved as part of a valid project. The entire package is successful executing within Visual Studio -- 37 green boxes. I move the package as file system .dtsx to my database as a stored package within MSDB and then schedule it as a job via SQL Agent invoking the MSDB stored package.
The package has a log file referenced in the scheduled job for errors and diagnostics while each of the 26 Transforms Data Tasks within the DTS 2000 packages writes to a fully qualified exception file on my server. The log and exception files have valid data when the package runs in Visual Studio but the exception files are untouched when the job fails and the log only references one of the 11 dependent steps with a job failed message. SQL tables are updated and files created from the Visual Studio execution but nothing is created when the job fails.
I am assuming there is a disconnect with the way I am moving the package to the scheduled job but I don't know what the problem is. Can anyone help?
Den
Script only displays the result for 'Master' database.Â
What changes are required for script to display result for all databases on the instance?
SELECT DB_Name() AS DatabaseName, OBJECT_NAME(ind.OBJECT_ID) AS TableName,Â
ind.name AS IndexName, indexstats.index_type_desc AS IndexType,Â
indexstats.avg_fragmentation_in_percentÂ
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstatsÂ
INNER JOIN sys.indexes ind Â
ON ind.object_id = indexstats.object_idÂ
AND ind.index_id = indexstats.index_idÂ
WHERE indexstats.avg_fragmentation_in_percent > 30 and indexstats.page_count >1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC