I know sysprocesses has the information, but I can't seem to find how this is done using the dmv's. sessions doesn have a db context, and requests is only active while activity is occurring.
Thanks!
I have a program which will show you users logged into a session. It's designed in MS Access and will also record citrix/terminal users. If you would like a copy of the program, download the attachment or email me at paulk@weccusa.org.
Paul Kohn
Database Administrator
I am trying to find a select on sysprocesses that would list all the activelogins. An active login is a login that has a TSQL statment being executedon the server,This didnt work to well! Any ideas. Thanks in advance.select sp.loginame,-- more columnsfrom master..sysprocesses spwhere sp.status not in ('sleeping','background' )order by 1
View 2 Replies View RelatedHi,
Imagine that I have a select something like this
select * from T where T.Name Like '%Maria%'
"Maria" would be what the user what to find. But for example in spanish we use Ã, ó, ú, ü... The same happens in french, german...
The user want to find Maria and does not care if another user (or himself) has inserted the client as Maria, MarÃa, Marïa or whatever.
Of course one solution is putting in the DB only the simple characters (a,e,i,o,u) and look for these, replacing the "strange" chars in the application to the "normal" ones. But if we want to be professionals we would need to insert in the DB the name in their original spelling (the image is the most important!)
Any idea, help or reference?
Thx in advance and happy new year.
David
Is there a query that I can run which will give me a list of users that have tried to use SQL resources for which they do not have permissions?
View 1 Replies View Related
Hello team, I have learned quite a bit from everyone here. I have decided to post a question for you guys and see what the best method is. Our Sr. DBA has asked me to get a list of all of our users permissions on all of our databases. We have about 20 SQL servers some of which have more databases and instances than I have fingers and toes. Can anyone recommend any solution to this problem? Perhaps there is a script I can download/write which will help me on this? Thanks in advance.
Hello,
Ho do I allow multiple users to share a database?
Background
I have developed a Windows App in VS.NET 2005 which connects to a SQL 2005 Express database.
Now I want to install the app and database on the network and I am getting an error "File 'file_name' is on a network device not supported for database files"
What is the best way to get this working
Thanks in advance,
Phil
We are currently running sql 2000 and are moving our database onto sql 2005 running on a different box.
We have managed to move the entire database, with users however the users permissions on specific tables/views/stored procedures have not been transferred, does anyone know a way of transferring user permissions rather then doing them all by hand?
The system is a large (over 500 table/views/stored procedures) and a very active one and therefore downtime is not optional.
any suggestions would be appreciated
Regards
Chris V
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
Thanks for your time..
Bidyut
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!!
View 6 Replies View RelatedHi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
Hi,
I am trying to create a block of sql statements that will read the sysdatabases table, and store the value of the database in a variable.
I want to use a cursor to step through all these databases (excep of course the control databases).
For each of the databases I want to run a backup log followed by dbcc shrinkfile. For dbcc shrinkfile, I need to change the database context.
What I am finding is that I cannot use the variable to hold the database name.
here is the code
-------------------------
SET NOCOUNT ON
DECLARE @db_name varchar(100)
DECLARE @db_filename varchar(100)
DECLARE list_dbs Cursor
FOR select [name] from master..sysdatabases
OPEN list_dbs
FETCH NEXT FROM list_dbs into @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
select name = @db_filename from @db_name..sysfiles
backup log @db_name with no_log
use @db_name
dbcc shrinkfile (@db_filename, 25)
FETCH NEXT FROM list_dbs into @db_name
END
CLOSE list_dbs
DEALLOCATE list_dbs
-----------------------
I get a syntax error where ever the @db_name is used (on the select and on the use).
Any suggestions?
Thanks,
Jim
Hello,
I have an XP box with SQL 2k working without any problems. I am trying to install SQL 2005 CTP April on the same machine. Installation goes fine, but when I try to connect, I am getting an error "Can not Create SSPI Context". Why am getting this error ? DOes the machine needs to be on a domain to get connected? If yes, is there a work around? Can I use mixed mode? WhereHow do I specify password for sa?
Hi all,
I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:
Error 5701
Severity Level 10
Message Text:
Changed database context to '%.*ls'.
However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).
Any ideas why this would start happening?
In reading Has_Perms_By_Name documentation in BOL, it appears that using a three-part name will make no assumption on the database context. However, when I run the code below, I get results that are database context sensitive.
Code Snippet
USE [master];
print has_perms_by_name('master.sys.sysusers', 'object', 'select')
print has_perms_by_name('msdb.sys.sysusers', 'object', 'select')
print has_perms_by_name('AdventureWorks.sys.sysusers', 'object', 'select')
print ''
USE [msdb];
print has_perms_by_name('master.sys.sysusers', 'object', 'select')
print has_perms_by_name('msdb.sys.sysusers', 'object', 'select')
print has_perms_by_name('AdventureWorks.sys.sysusers', 'object', 'select')
print ''
USE [AdventureWorks];
print has_perms_by_name('master.sys.sysusers', 'object', 'select')
print has_perms_by_name('msdb.sys.sysusers', 'object', 'select')
print has_perms_by_name('AdventureWorks.sys.sysusers', 'object', 'select')
The results are:
1
1
1
1
1
1
0
0
0
I created a login called 'temp' and added a user for this login in the AdventureWords database. I then added a securable for sys.sysusers for this user in the Adventureworks database. I then selected the "Deny" checkbox for the Select permission. I connected as temp and ran the T-SQL above.
It appears that the database name is being ignored when included in the name of the securable. What am I missing? Is this a bug?
My version: Standard SQL Server 2005 9.0.3054 on Windows 2003 R2 SP2 (3790).
Thanks,
Randy
Hi,
One of our client application launches a execuatable on server by calling master..xp_cmdshell. We are migrating to SQL 2005 and I would like to use the Context Switching feature
We have sql login €œclient€? which is used by clinet application and has very least privelge and have only required access to requried database and tables.
xp_cmdshell is disabled by default on our server and I would like to enable xp_cmdshell, launch the executable on the server and disable xp_cmdshell.I have created another SQL Login €œBob€? and is member of sysadmin. The below SP is created by logging in as €œBob€?. My objective is to grant execute access on this SP and grant impersonate access to sql login €œclient€? . So that client application connecting to SQL server can launch the executable by calling the SP
create proc Bob.[Bob_enable_xp_cmdshell]
with execute as 'Bob'
as
EXEC sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
-- launch the application using xp_cmdshell and disable the xp_command shell
When I try to execute (exec [Bob].[jtr_enable_xp_cmdshell]
) the above SP by logging in as €˜Bob€™, I€™m getting the below error
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 4
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 6
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 7
You do not have permission to run the RECONFIGURE statement.
Please note the SQL Login €œBob€? is a member of sysadmin fixed role. Thank in advance for you help.
With regards
GK
Hi,
I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:
EXECUTE AS LOGIN = 'link_user'
SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)
But when I use the exact same statements in a stored procedure I get the folllowing error:
Msg 7416, Level 16, State 1, Procedure linktest2, Line 5
Access to the remote server is denied because no login-mapping exists.
I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!?
Is there another workaround then to create all users in the linked server security?
Rgds
Bertrand
I am in a bind here..
I am getting the message (after do any sort of query) Database context changed to <dbname> I am using PHP as the p language.
Does anyone have any clue on why it is doing that..
Recently my host upgraded there PHP to version 4.3.10 and before the upgrade the queries were working fine.
Im on a windows server that runs PHP and im using a MSSQL database.
I've checked the bug reports on PHP and didn't come up with anything as well as googling the error..
IF you have any insite on this problem please let me know.. It may be a stupid error that I am overlooking on my part.
Thanks in advance
SImon writes "I'm unable to take a user database offline because there are system process using it and I can't identify or kill the system processes"
View 1 Replies View RelatedI have 30+ databases in sql 2000.
I want to dynalically loop though each of the databases and alter a column named 'EmpName' for all the 'Employees' tables in that database.
Here is my attempt.use Master
go
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)declare @TABLE_NAME NVarchar(100)
DECLARE curDB CURSOR LOCAL FORWARD_ONLY STATIC FOR
SELECT name FROM sysdatabases
OPEN curDB
FETCH NEXT FROM curDB INTO @dbname
While @@FETCH_STATUS=0
Begin
SET @SQL2 = 'USE ' + @DBNAME + ' ALTER TABLE ' + @TABLE_NAME + ' MODIFY EmpName varchar(100)'
from information_schema.tables where table_type = 'BASE TABLE' and table_name like '%Employees%' PRINT @SQL2
EXECUTE SP_EXECUTESQL @SQL2
fetch next from curDB into @DBName
end
Close curDB
End
When i execute the above script in master, it says 'Query executed successfully' but when i see the all the Employees table in each of the database, the required change is not made.
Can anybody explain this or give an alternate solution?
I have database in the database there are a few users that no one is used. When I try to drop thpse users I got next error message:
"The database principal is set as the execution context of one or more procedures, functions, or event notifications and cannot be dropped." (Msg 15136)
Indeed, I think that those users have execute rights on store procedures.
How do I find for wich procedures or other database objects those users have grants?
How do I delete them from database (and maybe from logins of the server)?
How can I see what grants a user has?
How can I see what grants does STP has?
I am using Sql Server 2000.
I have about 25+ databases . I want to run a series of commands on each database... how can I change the database context - the current database - dynamically in a loop...
something like this below:
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)
declare @TABLE_NAME NVarchar(100)
While @@FETCH_STATUS=0
Begin
SET @SQL2 = 'USE ' + @DB_NAME + ';GO;ALTER TABLE ' + @TABLE_NAME + ' MODIFY Name varchar(100)'
PRINT @SQL2
EXECUTE SP_EXECUTESQL @SQL2
End
I am getting an error when i run the above commands:
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'GO'
Can someone give me the correct solution?
Hi,
I have a MSSQL database running in a Microsoft cluster. I was working on troubleshooting for another SAP problem when they suggested I change the setting using hte SQL Server Client Network Utility to 'named pipes' from 'tcp/ip'. Every since, despite changing it back I'm unable to start the database in the cluster.
I'm kind of a newbie at this so I'd really appreciate any help in getting this database up and running.
Here is the error message from the event log:
sqsrvres] checkODBCConnectError: sqlstate = HY000; native error = 0; message = [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
USE master
Declare @db as nvarchar(258) = quotename (N'tempdb')
EXEC (N'USE' + @db + N'; EXEC(''SELECT DB_NAME();'');');
I have a login that is mapped to a Windows sysadmin account. I used it to login to Sql Server 2005. I then created a database called Freedom. I then added a Windows login and user called FreedomAdmin, with Freedom set as the default database. When I login in to Windows using FreedomAdmin and then try to login in to Sql Server 2005, I get the following error:
The server principal "FREEDOM1FreedomAdmin" is unable to access the database "master" under the current security context.
If the default database for FreedomAdmin is Freedom (and it is - I checked from my sysadmin login account), why can't I login. Must I give FreedomAdmin permissions to master?
I have a restriced user on SQL Server that is only permitted to creat a new database and manage it only. All other database are hidden to that user.
when that user login to SQL Server and create a database and try to change the default folder path for data and log files, gets an error,
The Server principal "User" is not able to access the database "model" under the current security context. (MSSQL Server, Error: 916)
Any idea???
Thanks,
Hi-I have a program that I am developing on a laptop, then deploying it on a server.I have the prgram running passing a username and PW in the connection object, but like the idea of using windows authentication MUCH better.I just joined the domain, so I am domain/me for example.If I log into the server, and look at securities, logins and added domain/me to the logins.I then try and set up a sql connection via both visual studio, and sql server magt studio, and get the dreaded "Cannot generate SSPI context" error. Anyone else have this problem? SHOULD V.S. be using domain/me to connect? TIA dan
View 6 Replies View RelatedSQL2005 on winserver 2003. I have a view in Xdb that accesses tables in 2 different databases (Xdb and Ydb) on the same server. I have mixed mode security. I have a SQL user (XYuser) that has read access to all tables and views on both databases, yet when I try to access the view using a C# windows application I get the following error:
The server principal "XYuser" is not able to access the database "Ydb" under the current security context
This same scenario works under SQL 2000. I looked through the postings and tried to set TRUSTWORTHY ON on both databases but that didn't help. I can access any other views or tables on the SQL 2005 server, just not the one that joins the tables cross databases. Any help is much appreciated... john
Hi try to connect with driver {SQL Sever Client} but i can't
I'm have SQL Server 2005 Express SP2
Error connecting to the ODBC server:[Microsoft][SQL Native Client][SQL Server] Changed database context to..
Please Help me
Thanks
Hi guys. I created a database "MyDatabase" using an AD Account "user01" which is a member of a domain group "GroupA". I have created a login in my SQL Server 2005 for this AD group. I have also added the said login as a user of my database. I created other AD users and put them in the same group. When I try to access the database using the other users, I get this message
The server principal "MyDomainuser02" is not able to access the database "MyDatabase" under the current security context.
This KB tells me that my situation is triggered by the first case in which the owner of the database is windows authenticated. I have already set the database to be TRUSTWORTHY. I haven't tried CERTIFICATES though. I believe I haven't done anything complex to warrant this solution.
I'm really lost with the myriads of principals and securables that SQL2005 has. All I want is just to execute a certain set of stored procedures. I can't do this because I can't even execute "USE MyDatabase". What are the permissions I need to give to "GroupA" ?
I'm trying to figure out how to identify the calling database within a sproc on a different database without using a parameter.
View 2 Replies View RelatedIn a new environment where we have 2005 servers and loads of databases that are running in compatibility mode with 2000 (80)
Are there any queries we can run on that can tell me what compatibility level each db is?
Looked on the web for ages apart from here but only info for changing the compatibility level - sp_dbcmptlevel – but queies only seem to be for changing it not and not reporting on it.
But I obviously do not want to do that for a production environment.
I want to do a mini report as to what db’s are still (I think all) 2000 mode and then suggest upgrades – I can go into the properties of each one but I know there must be an easier way – finding a load of admin queries that require cross apply and for that I need 2005 (90) compatibility….or is that only for the master db?
Anybody figure a way around this:
In a development server you take a backup and you want to move the entire database to a production server. The production server does not contain the database, users, or logins in master.
When you restore the backup to production in 7.0, the users get moved to DBO because the logins do not exist and then you cannot delete them.
We are having to drop the users from the database on development, back up the database and restore it to production, then recreate the users on production.
This is bogus and did not happen in 6.5 because of the aliases.
Anybody come up with a better way?