I love this article: http://www.sqlservercentral.com/articles/Temporary+Tables/66720/ and a must read for a DBA.
Feb 022010
What i meant was, how does any one see the users inside a Active Directory / NT Group. For example we have a group named BI_AD_GROUP with domain as SPONGBOB, so the group can be accessed as [SPONGBOB\BI_AD_GROUP].
you must have sysadmin Privileges and attitude to view this info.
you might be gearing up for a complex T-SQL, which will do the work, but instead it is just a simple one liner code.
xp_logininfo ‘SPONGBOB\BI_AD_GROUP’,’members’
The Group has to already exist in SQL Server as a login, to view its members.
This SQL will give you the list of users in the Group something like this:
| account name | type | privilege | mapped login name | permission path |
| SPONGEBOB\USER1 | user | user | SPONGEBOB\USER1 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER2 | user | user | SPONGEBOB\USER2 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER3 | user | user | SPONGEBOB\USER3 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER4 | user | user | SPONGEBOB\USER4 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER5 | user | user | SPONGEBOB\USER5 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER6 | user | user | SPONGEBOB\USER6 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER7 | user | user | SPONGEBOB\USER7 | SPONGBOB\BI_AD_GROUP |
| SPONGEBOB\USER8 | user | user | SPONGEBOB\USER8 | SPONGBOB\BI_AD_GROUP |
Now, the greed never ends. we want to view all the users in all the AD Groups..
I am going to get all the nt groups in a temp table and go from there.
select name into #getxplogininfo from master..syslogins where isntgroup=1
select * from #getxplogininfo
now you can write a cursor based on the info or something like this:
SELECT ‘xp_logininfo ‘+””+name+””+’,'+””+’members’+”” from #getxplogininfo
Copy and paste the result in a new query windows and execute or however you want to do it.
A similar article i wrote about re-naming the columns with double quotes HERE
Here is an example of what i want to say..
all of my columns contain Double Quotes in this example and i want to get rid of the double quotes at once.
USE <DatabaseName>
select ‘update SQLServerLifecycle set ['+column_name+']=replace(['+column_name+'],’+””+’"’+””+’,'+””+””+’)’
from information_Schema.columns where table_name =’SQLServerLifecycle’
To see all the query List colors:
Get the results in TEXT (Or Not its your choice)
Copy the Result and Paste in New Query window and Execute. (By Gods Grace, everything will work out fine)
You can also search for articles based on SSIS, which can do that during the Load of the information.
Here is a situation, i wanted to blog about. Renaming a database physical file. some may ask, why would anybody do it. Well for the people who work like “alice in wonderland”, everything is streamlined without hiccups, but for a volatile environment like i work in anything is possible. so back to the question of why would anybody rename the database, well there are certain situations as follows:
We will be taking the example of 2 databases ProjectDatabase_DEV and TEST as shown. one is a development database and the other is a test database as per the SDLC.
Developers working on limited resources use shared SQL Server environment and if the Project name has changed, so will be database name too, this is to streamline things a bit. When the objects are moved from _DEV to _TEST to production server, we have to take care of renaming and placing the database files appropriately:
Let us start with our databases: execute this command sp_helpdb ProjectDatabase_DEV
Now consider a scenario, where the team has decided to move this database into Production.
Steps you will take are take a most current full backup of the database and restore it on the production system.
(I am not going to talk about the users and logins and dbcc etc. this is just about renaming the db files)
Now we restore the database on the production server as ProjectDatabase.
There are 2 ways to restore the database : SSMS and T-SQL. I will use T-SQL for reference here:
Let us assume the database is restored to production using SSMS or T-SQL as
RESTORE database ProjectDatabase from disk = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ProjectDatabase_DEV.bak’
Now check the database on production server with sp_helpdb ProjectDatabase
you see the database Logical name still contains the _DEV as well as the Physical File Name.
So what do we do here: Things are simple: using T-SQL we change the logical and physical names as follows:
ALTER DATABASE ProjectDatabase
MODIFY FILE (NAME=’ProjectDatabase_DEV’, NEWNAME=’ProjectDatabase’)
After you execute the above statement, run sp_helpdb again, and here is the Output (see the Logical Name has changed)
Now we will change the Physical File name on the right:
ALTER DATABASE ProjectDatabase
MODIFY FILE (NAME=’ProjectDatabase’, FILENAME=’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\ProjectDatabase.mdf’)
Check again with sp_helpdb and here is the output (see the physical file name has change)
But the Physical file residing in the Data Folder have not changed: WHY?. This is because the database file name has been changed in the system tables and not the file itself.
After a couple of week of SQL Server running smoothly, the sysadmins re-started the Server and suddently no one was able to access the database anymore. sp_helpdb produced no results and gave permission error etc. This is how it looked:
no one was able to see the objects inside the database. Even if you try to check out the properties you see this:
so to correct this was to stop SQL Server Service. Rename the Physical mdf and ldf files to the required names as were defined previously. Then start the SQL Server service, and LO!, you are able to access the Database.
The best practise is to check the Files names and paths while getting the RESTORE done either using T-SQL as below or SSMS.
RESTORE Database ProjectDatabase from disk = ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ProjectDatabase_DEV.bak’
WITH REPLACE, – This is Optional and is only required on existing databases.
MOVE ‘Logical MDF Name’ to ‘Path to Physical FileName’,
MOVE ‘Logical LDF Name’ to ‘Path to Physical FileName’
I usually monitor the Counter Log Trace files from windows Performance monitor (perfmon) across several servers.
I have them written to a delimited csv file and then dump them as is to a SQL Server table for analysis.
This is how the column names were before " SERVER1 Memory Available MBytes" and " SERVER1 PhysicalDisk(_Total) % Disk Time". The double Quotes are included in the column name.
I had to find a way to format the column names with out the Quotes. After spending some time looking at the Query here is the final workaround. This will be helpful if you are looking at renaming many columns at once.
Let us see how the column name looked before i changed them.
select column_name from information_schema.columns where table_name = ‘ServerBaseLine’
The Column names are including the "Double Quotes". so i will have to stip the souble quotes as well as the space before the column name starts.
|
" SERVER1 Paging File( ?? C pagefile sys) % Usage" |
If you have to rename a single column this is how you do it.
sp_rename ‘tablename.columnname’,’newname’,’object_type’ Or with example
sp_rename ‘ServerBaseLine_2009110208.[" SERVER1 PhysicalDisk(_Total) % Disk Time"]‘,
‘SERVER1 PhysicalDisk(_Total) % Disk Time’,'COLUMN’
you have to QUOTENAME the Column Name or it will not be recognized. So here is the quick method of renaming the columns in the table all at one.
| Select ‘EXEC sp_rename ‘+””+table_name+’.'+quotename(column_name)+””+’,'+quotename(replace(replace(column_name,’" ‘,”),’"’,”))+’,'+””+’COLUMN’+””+CHAR(10)+’GO’ from information_schema.columns Where table_name = ‘ServerBaseLine’ |
Execute this Query and get the results in the TEXT format.
Copy the results from the RESULT windows and past it in another Query window and EXECUTE.
There you have it, all your columns are renamed at once.
There are other methods for moving logins, like using DTS/SSIS, but here is a Quickie with T-SQL. If we use DTS between diff version of SQL Servers, the Passwords will be copied over but the Original SID’s will not be and hence the database users will be considered ORPHANED. So you have to follow the T-SQL path to re-map the users.
Moving logins has been easier with these scripts from Microsoft, to transfer the logins between SQL Servers.
The Scripts (sp_hexadecimal and sp_help_revlogin) are located here http://support.microsoft.com/kb/246133
- Remember the Order of Execution of Scripts: (though doesn’t matter)
- sp_hexadecimal goes first
- sp_help_revlogin goes next
- Change the Result to TEXT (IMP)
- Run this command : EXEC master..sp_help_revlogin
- Copy the Results or save them and
- Execute the saved/copied results from the Script on the new Query window.
- The Logins and passwords should be created in the server, if not already present.
- OR Just copy the logins that is required
- verify database users-mapping with
- use <database name>
- exec sp_change_users_login ‘report’
- To check for orphaned users. This will list out to report all orphaned users in the database.
If there is a orphaned user in the list : use the below Query to re-map the Logins
sp_change_users_login ‘update_one’,'username’,'username’;
There are several ways to get this info: I am going to use sysprocesses in these examples, but you can use the new sys.sysprocesses (SQL 2005)
Select s.text,* from sys.sysprocesses p
Cross apply sys.dm_exec_sql_text (p.sql_handle) s
where p.spid >50
and p.spid not in (select @@spid)
Order by p.last_batch desc
OR
DECLARE @spid1 int
SET @spid1 = (SELECT top 1 SPID from master..sysprocesses order by last_batch desc)
DBCC INPUTBUFFER (@spid1)
OR
DECLARE @spid int
DECLARE @handle binary(20)
SET @spid = (SELECT top 1 SPID from master..sysprocesses order by last_batch desc)
SET @handle = (SELECT sql_handle from master..sysprocesses where spid = @spid)
SELECT * FROM ::fn_get_sql(@Handle)
OR in SQL 2005 Exclusive combine sys.dm_exec_requests and sys.dm_exec_sessions to get the Query text information with the latest active(executed) queries.
select sq.text,r.* from sys.dm_exec_requests r
join sys.dm_exec_sessions s
on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) sq
where r.session_id >50
and r.session_id not in (Select @@spid)
order by s.last_request_start_time desc
Loading ...