This is just a quick post about a simple why to find out what members are in an Active directory group.
EXEC master.dbo.xp_logininfo'MyDomain\MyGroup','members'
This will tell you what groups a user is in:
EXEC master.dbo.xp_logininfo‘MyDomain\MyUser’,‘all’
Greetings and other Awesome Salutations,
It has been a while because I am learning oodles of new stuff. There are a few things that I don’t want to forget so here goes.
CHARINDEX is a super easy way to get the starting location of something within a string. For example, I was looking for the last half of Superhero names. My column is SuperheroName in the Comics Table and pretend there are dashes in their names, please just for this example – I know there aren’t really dashes please continue to love me with my flaws.
So…
SELECT TOP 4 SuperheroName
FROM Comics
Results:
Bat-man
Bat-girl
Super-man
Super-girl
Now the magic, CHARINDEX will give you the location of the character you specify.
SELECT TOP 4 CHARINDEX('-', SuperheroName)
FROM Comics
Results:
4
4
6
6
How cool is that?!! But I want to pull back everything after the “-” character, which means I have to get tricky because CHARINDEX will only give me the location number of where it is within the string. SUBSTRING will save the day (Tada!). SUBSTRING is really cool. I am going to pass it ColumnName, Starting Position (using CHARINDEX), Ending Position (using LEN and the column name that will get me the length of that column on that row). So it looks like this:
SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName), LEN(SuperheroName))
FROM Comics
Results:
-man
-girl
-man
-girl
But that still isn’t quite what I want…so I am going to add 1 to the Starting point position number (this will allow me to skip over the character I am using):
SELECT TOP 4 SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics
Results:
man
girl
man
girl
Oh but wait, there is more! I think it is really silly to have the dashes in the first place so check this out:
SELECT TOP 4
SUBSTRING(SuperheroName,1,CHARINDEX('-',SuperheroName)-1) + SUBSTRING(SuperheroName,CHARINDEX('-',SuperheroName)+1, LEN(SuperheroName))
FROM Comics
Results:
Batman
Batgirl
Superman
Supergirl
Holy Rusted Metal Batman! What did we do? This time in my substring I used the first position and then the CHARINDEX -1 to tell me what was right before the dash. Then I combined them! With these super powers combined…well awesome things happen.
Enjoy!
Have you ever remembered what Intstance a table was on but not the database name? This will solve your problem. First you will create the table, then the procedure that will look for it and finally, you will execute the procedure with your table name. (At the end you can drop it so no one knows your secret.)
It returns the database, the schema, the table name and a type.
SET NOCOUNT ON
CREATE TABLE SearchTable(TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, TABLE_TYPE varchar(50)) --If you haven't ever used this.
GO
CREATE PROC usp_FindMyTable (@TABLE_NAME sysname = null)
AS
SET NOCOUNT ON
TRUNCATE TABLE SearchTable -- If you are using it again.
IF @TABLE_NAME IS NULL
BEGIN
PRINT 'No Table to look for. Please supply a table name. Like: ' + CHAR(13)
+ ' EXEC usp_FindMyTable Orders'
GOTO usp_FindMyTable_Exit
END
DECLARE @MAX_dbname sysname, @dbname sysname, @sql varchar(8000)
SELECT @MAX_dbname = MAX([name]), @dbname = MIN([name]) FROM master..sysdatabases
WHILE @dbname @dbname
END
SELECT * FROM SearchTable
usp_FindMyTable_Exit:
SET NOCOUNT OFF
RETURNGO
EXEC usp_FindMyTable 'Unicorn' -- put in the table you are searching for here. GO
SET NOCOUNT OFF
DROP PROC usp_FindMyTable --Clean up so no one knows
DROP TABLE SearchTable --Really, it is our secret. More clean up.
GO
Greetings to the Kingdom!
Huge thanks to my friends Dan and Tamie for this one. It will help you determine the TCP Port for an instance. I love my friends in the SQL Community and am so happy they are willing to share!
DECLARE @key VARCHAR(50), @RegistryPath VARCHAR(200)
IF (SERVERPROPERTY('INSTANCENAME')) IS NULL
BEGIN
SET @RegistryPath='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
END
ELSE
BEGIN
SET @RegistryPath='Software\Microsoft\Microsoft SQL Server\'+CONVERT(VARCHAR(25),SERVERPROPERTY('INSTANCENAME'))+'\MSSQLServer\SuperSocketNetLib\Tcp\'
END
EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', @RegistryPath, 'tcpPort'
Have a magical day!
Last week I did something I promised myself I would never do again. It was horrible, dirty and made me feel like the worst DBA in the world. I didn’t drop a production database or truncate the most important table, this was way worse. I created a cursor. I am hoping that can I appeal to your kindness and understanding. Please help me forgive myself so the healing may begin.
It all started when I needed to build an Error Log report in SSRS. We are using a PowerShell script to go out to all our servers and bring a copy of the latest SQL error log to a centralized database and table. I was making a call to this table and only pulling back the last 4 days worth of data. I have learned over the years that with SSRS if you try to feed it too much data, you don’t get a report, you get a big red “X”. I was getting that “X”. Since I wasn’t allowed to modify the base table incase we needed to review it for other troubleshooting matters, I created a new table that just had the last 4 days worth of data. I was still getting the red “x”. I knew I had to filter my data more. We have a table of specific errors that we want to show up on this report. But they are not the complete error, just parts of it so the error remains generic enough to pull it from any server. In order for me to compare these fields, I need to use a “LIKE” clause. I tried joining with the “LIKE” clause, but I didn’t have any luck. I even tried a “Cross Join” and a “Hard Join” but it still didn’t work.
In my despair, I was talking to a friend of the kingdom and he suggested a cursor. I backed away in shock. “No, no! Cursors are bad!” I cried. He calmly looked at me and said, “You are trying to use a sledge-hammer to hang a picture right now, try the cursor.”
I sat back down, arguing with myself. Could I do this? Betray what I have always believed and actually use a cursor? I figured I would at least write one and hope that I came up with a better solution in the process.
I know you are all excited to see the monster that I have created, truly a Royal Pain:
SET ANSI_PADDING ON
GO
Declare @objid int
Declare @ErrorMsg Varchar (255)
DECLARE SQLErrorLog_Cursor CURSOR FOR
/*Here is where I load my table of filters. This will allow me to add more errors to the report later without rebuilding anything.*/
SELECT [objid],[ErrorMsg]
FROM [LightsOn].[dbapp].[SQLErrorLogParseList]
OPEN SQLErrorLog_Cursor
FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
WHILE @@FETCH_STATUS = 0
BEGIN
/*This is my main query that I need to apply the filter to. InstanceSQLLog is the really large table I am filtering down.*/
INSERT INTO dbapp.SQLErrorLogFilter
SELECT InstanceName, LogDate, Text, P.FullName AS PrimaryOps, P.ID AS PrimaryOpsID,P3.FullName AS SecondaryOps, P3.ID AS SecondaryOpsID
FROM dbapp.InstanceSQLLog ISL
LEFT JOIN dbapp.Instance I ON ISL.InstanceId = I.InstanceID
LEFT JOIN People P ON I.PrimaryOpsDBAId = P.Id
LEFT JOIN dbo.People P3 ON I.SecondaryOpsDBAId = P3.ID
WHERE LogDate > GETDATE()-4 AND TEXT LIKE '%' + @ErrorMsg + '%'
FETCH NEXT FROM SQLErrorLog_Cursor
INTO @objid, @ErrorMsg
END
CLOSE SQLErrorLog_Cursor
DEALLOCATE SQLErrorLog_Cursor
GO
This is one of the steps of a 4 part job that I auto run early in the morning so the reports are in the specified DBA’s Email when they arrive at work (each report is customized to the DBA and the servers that are their Primary responsiblity.).
The whole process goes like this: I truncate the dbapp.SQLErrorLogFilter Table, run this cursor, Truncate the final grouped and filtered table for the report and repopulate that so SSRS gets a very clean and easy to read table. The whole process takes about 8 minutes. I am still trying to find a better way, but this works for now and the team is happy.
I have been toying with the idea of adding FastForward to the cursor. Does anyone have any opinions of why I should or should not do that?
Thank you for being so kind to read. I love how included the community makes me feel and how much everyone is willing to teach each other.
Have a magical week!
Andrea
Well one script to find them anyway… I wanted to share a TSQL script that I use ALL THE TIME to quickly and easily find specific text in the names or code of all objects in a database. This script has countless uses, but what I use it for most often is to track down certain tables, views, or stored procedures, that are referenced in other objects.
For example, If I have been making updates to a particular view and I’m pretty sure those updates are going to break a number of views, stored procedures, etc, I will enter the name of the view I am updating as the parameter. This script will return any object that contains that text so I can review each and make any additional needed changes to ensure reports or applications continue to run smoothly.
The script looks for text in all names of objects as well as looking at the code for objects such as views, stored procs, functions, triggers, etc. Pretty slick. I hope you find it helpful.
USE DBName
DECLARE@SearchText VARCHAR(50)
SET@SearchText = '%EnterTextHere%'
--In Script
SELECT DISTINCT OBJECT_NAME(id) AS ObjectName,type_desc AS ObjectType,'In Script' ASCategory
FROM SYSCOMMENTS S
INNER JOINSYS.OBJECTS O ON O.Object_Id = S.id
WHERE S.text LIKE @SearchText
UNION
--In Object Name
SELECT Name AS ObjectName,type_desc AS ObjectType,'In Object Name' ASCategory
FROM sys.all_objects
WHERE name LIKE @SearchText
UNION
--Table Columns
SELECT O.name AS ObjectName, O.Type_Desc AS ObjectType, 'In Column: ' + C.name AS Category
FROM sys.all_objects O
INNER JOINsys.all_columns C ON O.object_id = C.object_id
WHERE C.name LIKE@SearchText
AND O.Type_Desc= 'USER_TABLE'
ORDER BY Category DESC, ObjectType,ObjectName
I have been rushing all week trying to get new development environments upgraded to 2012 while leaving the old environments online. Everyone wants it done now and I wanted to share a few things that are making my job easier.
This first one is a script that makes copying logins across servers so easy. I found this online and feel terrible that I can’t properly accredit it to the person that wrote it. (If you wrote it, let me know and I will update this)
USE master
GO
IF OBJECT_ID (‘sp_hexadecimal’) IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = ‘0x’
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = ‘0123456789ABCDEF’
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint – (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID (‘sp_help_revlogin’) IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name <> ‘sa’
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( ‘S’, ‘G’, ‘U’ ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT ‘No login(s) found.’
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = ‘/* sp_help_revlogin script ‘
PRINT @tmpstr
SET @tmpstr = ‘** Generated ‘ + CONVERT (varchar, GETDATE()) + ‘ on ‘ + @@SERVERNAME + ‘ */’
PRINT @tmpstr
PRINT ”
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ”
SET @tmpstr = ‘– Login: ‘ + @name
PRINT @tmpstr
IF (@type IN ( ‘G’, ‘U’))
BEGIN — NT authenticated account/group
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ FROM WINDOWS WITH DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
END
ELSE BEGIN — SQL Server authentication
— obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, ‘PasswordHash’ ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
— obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN ‘ON’ WHEN 0 THEN ‘OFF’ ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = ‘CREATE LOGIN ‘ + QUOTENAME( @name ) + ‘ WITH PASSWORD = ‘ + @PWD_string + ‘ HASHED, SID = ‘ + @SID_string + ‘, DEFAULT_DATABASE = [‘ + @defaultdb + ‘]’
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_POLICY = ‘ + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ‘, CHECK_EXPIRATION = ‘ + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN — login is denied access
SET @tmpstr = @tmpstr + ‘; DENY CONNECT SQL TO ‘ + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN — login exists but does not have access
SET @tmpstr = @tmpstr + ‘; REVOKE CONNECT SQL TO ‘ + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN — login is disabled
SET @tmpstr = @tmpstr + ‘; ALTER LOGIN ‘ + QUOTENAME( @name ) + ‘ DISABLE’
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
EXEC sp_help_revlogin
This next one is awesome too. When you have copied a database to a new server and have SQL logins that you want to sync up really quick, this script is awesome. Run it on each database that has that login.
ALTER USER [LoginName]
WITH LOGIN = [LoginName]
I hope this will save you some time and helps you get to the ball too!
Have a magical day!
Andrea Allred presents RoyalSQL 